On Oracle 184.108.40.206 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 always used TKPROF to do SQL tuning in previous versions of
Oracle (7,8 and 9). Can I still use TKPROF in Oracle 10g R2?
A user complains that every time he tries to access a table (select only), it takes more than two hours to get
the results. There are no DML ...
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...
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.