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
/
Pavan DBA's Blog 
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