How to Resolve ORA-03297 When Resizing a Datafile by Finding the Table Highwatermark

(Doc ID 130866.1)

Last updated on FEBRUARY 20, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.5.0 and later
Information in this document applies to any platform.



Goal

In a number of cases it is desired to shrink an oversized datafile. The challenge is then up to find to which size the datafile can be shrunk? Using the trial and error approach will likely give:

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


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).

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms