How to shrink Oracle datafiles to their highwatermark

Compute the highest allocated block per datafile from DBA_EXTENTS and generate RESIZE statements that shrink each datafile to just above that point. This query itself is read-only — no changes happen until you copy the generated statements and execute them.

State-changing when the generated statements are run. RESIZE fails if the target is smaller than the highest extent, so review each statement first and consider MOVE / SHRINK on the largest segments before shrinking the datafile.

SELECT 'ALTER DATABASE DATAFILE ''' || file_name || ''' RESIZE '
       || ROUND(highwater + 2) || 'M;' AS ddl
FROM (
  SELECT /*+ RULE */
         a.tablespace_name,
         a.file_name,
         a.bytes / 1024 / 1024 AS file_size_mb,
         (b.maximum + c.blocks - 1) * d.db_block_size / 1024 / 1024 AS highwater
  FROM   dba_data_files a,
         (SELECT file_id, MAX(block_id) AS maximum
          FROM   dba_extents
          GROUP BY file_id) b,
         dba_extents c,
         (SELECT value AS 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
);