shrink_ts

col NAME for a110
SELECT 'alter database datafile '''||file_name||''' resize ' || CEIL( (NVL(hwm,1)*8192)/1024/1024 )||'m;' as NAME, CEIL( blocks*8192/1024/1024) currsize, CEIL( blocks*8192/1024/1024) - CEIL( (NVL(hwm,1)*8192)/1024/1024 ) savings
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 a.TABLESPACE_NAME='&tbs' order by 3 desc;