Pavan DBA's Blog

The DBA Knowledge Store

script to check possible resize value for a datafile

Posted by Pavan DBA on March 30, 2012


Sometimes, we will be getting a requirement to resize the datafile to a lower value in order to create some space in the mount point (file system). In that case, how we would know which datafile and to what size we can lower it? The below script will helps us in that….

###################################################

SCRIPT TO CALCUTE POSSIBLE RESIZE VALUE

###################################################

 

 

set verify off

column file_name format a50 word_wrapped

column smallest format 999,990 heading “Smallest|Size|Poss.”

column currsize format 999,990 heading “Current|Size”

column savings format 999,990 heading “Poss.|Savings”

break on report

compute sum of savings on report

 

column value new_val blksize

select value from v$parameter where name = ‘db_block_size’

/

 

select file_name,

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

ceil( blocks*&&blksize/1024/1024) currsize,

ceil( blocks*&&blksize/1024/1024) –

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

/

 

select file_name,

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

ceil( blocks*&&blksize/1024/1024) currsize,

ceil( blocks*&&blksize/1024/1024) –

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

from dba_temp_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

/

 

column cmd format a75 word_wrapped

 

select ‘alter database datafile ”’ || file_name || ”’ resize ‘ ||

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || ‘m;’ cmd

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

and ceil( blocks*&&blksize/1024/1024) –

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0

/

 

 

select ‘alter database tempfile ”’ || file_name || ”’ resize ‘ ||

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || ‘m;’ cmd

from dba_temp_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

and ceil( blocks*&&blksize/1024/1024) –

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0

/

 

Advertisements

2 Responses to “script to check possible resize value for a datafile”

  1. mohanasowri said

    hi pavan sir,
    this is sowri,i have a doubt in exports and imports(traditional jobs), that is while exporting data i didnt mension the logfile and how to know which level(tablespace or schema or table) i have exported without logfile.
    and one more question,my PROD side tablespace name is different and my QA side tablespace name is different,now i have exported tablespace level at PROD side,now i want to import that data at QA side,how we should do if the tablespace names are different.
    pls give me the solution
    thankq in advance.

    • Pavan DBA said

      Hi Sowri,

      This is the way you need to follow if you want to know whether export is schema/database etc levels

      1. run this command
      imp file=”your dump file name” log=”give any name for logfile” show=y

      the above command will work if it is full=y, that means your export is database level. otherwise it will throw error asking to give fromuser/touser (so it is schema/table level)

      2. I am not sure if you are asking about this in exp/imp or datapump, so let me answer both

      in exp/imp, if the tablespace names are different between PROD and QA side, still the database will go to the default tablespace (i.e tablespace with different name) into QA. but this is not applicable for some table types i.e to import following type of tables, mandatorily u need to have same tablespace name as PROD
      a. partitioned table
      b. IOT
      c. cluster table
      d. tables with LOB datatype etc

      in expdp/impdp, we don’t have this issue and happily we can use REMAP_TABLESPACE parameter

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: