Skip to content

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

  • by

ORA-03297

There're two error patterns of ORA-03297, one is to resize a data file, the other is to shrink a temp file.

Resize Data File

The first case of the error is to RESIZE a datafile to a smaller one, but it failed with ORA-03297.

SQL> select bytes/1024/1024 "SIZE(MB)" from dba_data_files where file_name = '/u01/app/oracle/oradata/ORCL/example01.dbf';

  SIZE(MB)
----------
       270

SQL> alter database datafile '/u01/app/oracle/oradata/ORCL/example01.dbf' resize 10m;
alter database datafile '/u01/app/oracle/oradata/ORCL/example01.dbf' resize 10m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

ORA-03297 means that some existing data is at the water mark higher than the size you want to resize or shrink, you have to set a higher value.

Shrink Temp File

Tried to shrink a tempfile, but it failed with ORA-03297.

SQL> alter tablespace temp shrink tempfile '/u01/app/oracle/oradata/ORCL/temp01.dbf' keep 10m;
alter tablespace temp shrink tempfile '/u01/app/oracle/oradata/ORCL/temp01.dbf' keep 10m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Solutions

1. Set a Higher Value

The target size you set in the first place might be too small to make it. To solve the error quickly, we can set a little higher value than it.

Setting a higher value to reduce the size of datafile or tempfile does not always succeed, you might need to try several higher and higher values before a success.

Resize Data File

May be reducing a data file from 270MB to 10MB is too ambitious, we try 200MB this time.

SQL> alter database datafile '/u01/app/oracle/oradata/ORCL/example01.dbf' resize 200m;

Database altered.

Shrink Temp File

SQL> alter tablespace temp shrink tempfile '/u01/app/oracle/oradata/ORCL/temp01.dbf' keep 1g;

Tablespace altered.

2. Purge Recycle Bin

Dropped tables in recycle bin may take significant space of the tablespace, you may purge recycle bin at different levels before trying again.

3. Reorganize Table and Index

To lower down high water mark of a segment, we can reorganize some tables or indexes to make them compact and decent. To reorganize segments, you may move tables or rebuild indexes. Or use data pump to export / import tables.

Then try again.

4. Coalesce Tablespace

It's to coalesce adjacent free consecutive space to a bigger one, but there may be not always have adjacent extents to coalesce. So the statement will succeed, but it may not help a lot.

SQL> alter tablespace example coalesce;

Then try again.

5. Omit KEEP Clause

This solution is only for shrinking temp files. You can omit the KEEP clause to let the database shrink the tablespace as much as possible.

SQL> alter tablespace temp shrink tempfile '/u01/app/oracle/oradata/ORCL/temp01.dbf';

Tablespace altered.

6. Recreate Temp File

Another way to solve ORA-03297 is to recreate temp file if you really want it exactly 10MB. Also, this solution is only for temp files.

Offline Tempfile

You can offline the temp file to make sure that no one is using it.

SQL> alter database tempfile '/u01/app/oracle/oradata/ORCL/temp01.dbf' offline;

Database altered.

Drop Tempfile

Since the data in an offline tempfile is useless, you can remove it anytime.

SQL> alter database tempfile '/u01/app/oracle/oradata/ORCL/temp01.dbf' drop including datafiles;

Database altered.

The tempfile is physically removed, too.

Add Tempfile

If the tempfile is important to us, we can add it back with the same name.

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/ORCL/temp01.dbf' size 10m autoextend on next 10m maxsize unlimited;

Tablespace altered.

Leave a Reply

Your email address will not be published.