Q
Problem solve Get help with specific problems with your technologies, process and projects.

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

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.

Dig Deeper on Oracle error messages

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close