Datafile high water mark

The "alter database datafile '...' resize nnn" command is great for increasing the size of a datafile as it fills. It can also be used to shrink a datafile if it has been over allocated. For obvious reasons, it cannot be shrunk past the maximum used block in the file.

You can use the following query to determine how much an over allocated file can be shrunk.

SQL> select file_name, hwm, blocks total_blocks, blocks-hwm+1 shrinkage_possible
  2  from dba_data_files a,
  3       ( select file_id, max(block_id+blocks) hwm
  4           from dba_extents
  5          group by file_id ) b
  6  where a.file_id = b.file_id;

FILE_NAME                             HWM TOTAL_BLOCKS SHRINKAGE_POSSIBLE
------------------------------ ---------- ------------ ------------------
/d04/dbs/temporary.dbf              64592        97920              33329
/d04/dbs/tools.dbf                   3547         3584                 38
/d04/dbs/users.dbf                 142316       142336                 21