Search notes:

ORA-03297: file contains used data beyond requested RESIZE value

Assume the following statement throws ORA-03297: file contains used data beyond requested RESIZE value:

alter database datafile '…' resize  14000 M;

Find the mininum size the data file can be shrinked to (8192 = block size):

select
   max(ceil( (ext.block_id + ext.blocks) * 8192 / 1024 /1024 )) mb
from
   dba_extents    ext    join
   dba_data_files fil on ext.file_id = fil.file_id
where
    ext.tablespace_name = 'DATA' and
    fil.file_name       = '…'
;

Use value of previous query:

alter database datafile '…' resize  18000 M;

If this value is still too large…

… find objects that occupy space or need to be moved to make room for shrinking the data file:

select
   ext.owner,
   ext.segment_name,
   ceil( ( ext.block_id + ext.blocks) * 8192 / 1024 /1024 ) mb
from
   dba_extents    ext                                    join
   dba_data_files fil on ext.file_id = fil.file_id
where
    ext.tablespace_name = 'DATA' and
    fil.file_name       = '…'
order by
   mb desc
;

Drop or move objects:

drop table…;
alter table … move;

Empty the recycle bin when tables were dropped:

purge recyclebin;

Resize:

alter database datafile '…' resize  1000 M;

ORA-03297: file contains used data beyond requested RESIZE value

See also