By
Published: 14 May 2007
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
I have a table with 1.5 million records that needs to have a column updated based on a correlated subselect. This update is currently sitting at four...
Continue Reading
I have a critical performance issue due to the large volume of data for a specific customer. Whenever customer XYZ's data is being fetched the query ...
Continue Reading
I increased the RAM from 2GB to 3GB, but when I try to increase the sga_max_size I get the following error on startup: "ORA-27102: out of memory."
Continue Reading