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

  • by

ORA-03297

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

SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ERPAPP2A/TEMP32.dbf' KEEP 10M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCLCDB/ERPAPP2A/TEMP32.dbf' KEEP 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 shrink, you have to set a higher value.

Solution

To shrink tempfile and overcome the error, you can take the following steps.

1. Offline Tempfile

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

SQL> alter database tempfile '+DATA/ORCLCDB/ERPAPP2A/TEMP32.dbf' offline;

Database altered.

2. Drop Tempfile

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

SQL> alter database tempfile '+DATA/ORCLCDB/ERPAPP2A/TEMP32.dbf' drop including datafiles;

Database altered.

The tempfile is physically removed, too.

3. Add Tempfile

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

SQL> alter tablespace temp add tempfile '+DATA/ORCLCDB/ERPAPP2A/TEMP32.dbf' size 10m autoextend on next 10m maxsize unlimited;

Tablespace altered.

Leave a Reply

Your email address will not be published. Required fields are marked *