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

I got ” ORA-03297: file contains used data beyond requested RESIZE value”  error in Oracle database.

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

Details of error are as follows.

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

Cause: Some portion of the file in the region to be trimmed is currently in use by a database object

Action: Drop or move segments containing extents in this region prior to resizing the file, or choose a resize value such that only free space is in the trimmed.

SQL> alter database datafile '/oradata/<SID>/users01.dbf' resize 117248K;
alter database datafile '/oradata/<SID>/users01.dbf' resize 117248K
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

file contains used data beyond requested RESIZE value

This ORA-03297 error is related with the Some portion of the file in the region to be trimmed is currently in use by a database object.

The error is due to fact that there are allocated extents between the size to resize/shrink to and the end-of-file (might be space management blocks, so not necessary belonging to a segment).

Alter database datafile resize

To solve this error, Drop or move segments containing extents in this region prior to resizing the file, or choose a resize value such that only free space is in the trimmed.

Two options do exist to find the highwater mark of a datafile:

– checking backwards for free space at end of file (proven to be the fastest)

– check highest allocated extent in datafile (it might be that space management blocks are above last allocated extend)

In case there are blocking segments for resizing/shrinking the datafiles the attached SQL-script: show_segment_above_size.sql will show the segments which do have extents above the desired size.

The script will show the ‘blocking’ segments for the resize, no block_id/location is shown due to fact that whole segments needs to be ‘moved/cleaned up/…’ in order to free up the extents thus enabling the desized resize.

1) checking backwards for free space at end of file

Due to added functionality (different blocksizes for tablespaces) the script has been split into:

• Oracle version 9 and higher
• Oracle version 8 and lower

To solve this error, you can purge the recycle bin and try again. 

Or  you can Try “alter tablespace” coalesce or “alter index” rebuild and try again.

If these are not solved your problem, then you should move tables and index to another tablespace.

You should read the following post to learn how to move tables and indexes to another tablespace.

How to Move Tables, Index Rebuild, Partitions and Subpartitions to Another Tablespace in Oracle

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )