Resize datafile to HWM



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%');

<br />
SET LINESIZE 150<br />
SET PAGESIZE 9999<br />
SET VERIFY   OFF</p>
<p>select 'alter database datafile '''||file_name||''' resize '||<br />
ceil( (nvl(hwm,1)*(SELECT value FROM v$parameter where name = 'db_block_size'))/1024/1024 )||'m;' cmd<br />
from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b<br />
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<br />
and lower(a.file_name) like lower('%&file_name%');<br />