generate resize datafile script - Oracle Database Script. SELECT 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+2)||' '||'m'||';' FROM (SELECT /*+ rule */ a.tablespace_name, a.file_name, a.bytes/1024/1024 file_size_MB, (b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater FROM dba_data_files a , (SELECT file_id, max(block_id) maximum FROM dba_extents GROUP BY file_id) b, dba_extents c, (SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') d WHERE a.file_id= b.file_id AND c.file_id = b.file_id AND c.block_id = b.maximum ORDER BY a.tablespace_name, a.file_name);
SELECT 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+2)||' '||'m'||';' FROM (SELECT /*+ rule */ a.tablespace_name, a.file_name, a.bytes/1024/1024 file_size_MB, (b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater FROM dba_data_files a , (SELECT file_id, max(block_id) maximum FROM dba_extents GROUP BY file_id) b, dba_extents c, (SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') d WHERE a.file_id= b.file_id AND c.file_id = b.file_id AND c.block_id = b.maximum ORDER BY a.tablespace_name, a.file_name);