Query to Shrink Datafiles

Below query can be used to generate ALTER statements for resizing datafiles that have either overgrown or were added with large initial size and were never used.

set lines 150 pages 1000
col command for a150
SELECT ceil( blocks*(a.BlockSize)/1024/1024) "Current Size",
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Smallest Poss.",
   ceil( blocks*(a.BlockSize)/1024/1024) -
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Savings",
   'Alter Database Datafile '''|| file_name || ''' Resize ' ||
      ceil((nvl(hwm,1)*(a.BlockSize))/1024/1024/100)*100  || 'M;' "Command"
FROM (SELECT a.*, p.value BlockSize FROM dba_data_files a
JOIN v$parameter p ON p.Name='db_block_size') a
LEFT JOIN (SELECT file_id, max(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id ) b
ON a.file_id = b.file_id
WHERE ceil( blocks*(a.BlockSize)/1024/1024) - ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 )
   > 1024 /* Minimum MB it must shrink by to be considered. */
ORDER BY "Savings" Desc;

Cheers,
Anurag