Oracle Database -- Shrink Datafiles

Problem

You need to shrink datafiles

Solution

Use the below script to generate statements to shrink the datafiles to the smallest possible size

SELECT    'alter database datafile '
       || file_id
       || ' resize '
       || real_bytes
       || ';'
  FROM (SELECT /*+ ordered */ d.file_id,
               d.bytes bytes,
               f.real_blocks * t.block_size real_bytes,
               d.bytes - f.real_blocks * t.block_size delta
          FROM dba_data_files d,
               dba_tablespaces t, 
               (SELECT file_id, MAX (block_id + blocks - 1) real_blocks
                    FROM dba_extents where rownum > 0
                    GROUP BY file_id) f               
         WHERE d.file_id = f.file_id and d.tablespace_name=t.tablespace_name)
 WHERE delta > 0