Resize datafile to HWM

March 1st, 2013

Commands to resize datafiles up to HWM

SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY   OFF

select 'alter database datafile '''||file_name||''' resize '||
ceil( (nvl(hwm,1)*(SELECT value FROM v$parameter where name = 'db_block_size'))/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*(SELECT value FROM v$parameter where name = 'db_block_size')/1024/1024) -ceil( (nvl(hwm,1)*(SELECT value FROM v$parameter where name = 'db_block_size'))/1024/1024 ) > 0
and lower(a.file_name) like lower('%&file_name%');


Comments are closed.