shrink_ts_ALL

col NAME for a100 
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(+) order by 3 desc;