How much can I shrink my datafile?

Periodically, we will need to shrink a datafile. In Oracle, you can only shrink a file to the point occupied by the “highest” block in the file. The SQL below will provide you with the SQL to execute for each datafile after calculating the size to which it can be shrunk.

set lines 1000 trims on pages 0
SELECT f.TABLESPACE_NAME,
       NVL(ROUND(f.bytes/1024/1024,0),0) AS fbytes ,
       NVL(ROUND(MAX(e.blocks + e.block_id)/1024/1024*t.block_size,0),0) AS used ,
       NVL(ROUND((f.bytes/1024/1024) - (MAX(e.blocks + e.block_id)/1024/1024*t.block_size),0),0) AS free,
       'ALTER DATABASE DATAFILE '''||file_name||''' RESIZE '||NVL(DECODE(ROUND(MAX(e.blocks + e.block_id)*t.block_size * 1.01 / 1024 / 1024,0),0,1,ROUND(MAX(e.blocks + e.block_id)*t.block_size * 1.01  / 1024 / 1024,0)+1),1)||'M;' AS "EXECUTE STATEMENT BELOW"
  FROM dba_extents e,
       dba_data_files f,
       dba_tablespaces t
  WHERE f.file_id = e.file_id(+)
    AND f.tablespace_name = t.tablespace_name
  GROUP BY file_name,f.tablespace_name,
           autoextensible,f.bytes,
           f.maxbytes, t.block_size order by 4
/

If you receive an error after executing one of the shrink commands above, this can be due to two reasons:

1. The recycle_bin occupies space higher than the size to which you are trying to shrink the file. purge the recycle_bin to fix this.
2. The file has had data added to it between the time of executing the query above and the time during which you are shrinking the file. The new amount of space used is above the amount calculated above.