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.