ORA-03297: file contains used data beyond requested RESIZE value
I have a datafile that is 16GB. I have moved indexes out and want to shrink the file, but get the error: "ORA-03297: file contains used data beyond requested RESIZE value."
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
Step 2 of 2:
On Oracle 9.2.0.7 Linux RH4.2 x86-4, I have a datafile that is 16GB. I have moved indexes out and want to shrink the file, but get the error: "ORA-03297: file contains used data beyond requested RESIZE value." Is there a safe way to reclaim that 4 GB and shrink the datafile? I would like to have smaller datafile. Thank you.
This is because the high water mark on the tablespace extends beyond 12 GB on the tablespace. If you were running Oracle 10g and had Automatic Storage Management on the tablespace and row movement enabled on the tables, you could issue an Alter Table Shrink. With Oracle 9i, unfortunately, it is not as safe and easy. The only options you have with Oracle 9i are to drop the table, resize the tablespace datafile, and then re-create the table; or drop the indexes, move the table to a different tablespace (if one is available with enough space) using Alter Table Move, resize the tablespace, and then move it back and re-create the indexes.
