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