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