ORA-03297 after PURGE and wanting to resize datafiles
From: Anthony Ballo <anthony.ballo_at_onecall.com>
Date: Fri, 20 Jan 2012 17:03:36 -0800
Message-ID: <CB3F493E.1A354%anthony.ballo_at_onecall.com>
Anyone running SOA 11g out there? Well, we just started working on a data purge strategy (PS4) using the Looped Purge script supplied by Oracle. Like others have written on the web, it doesn't delete everything so I put together a "manual purge" script that deletes the data and then does a SHRINK and DEALLOCATE UNUSED on various tables.
Date: Fri, 20 Jan 2012 17:03:36 -0800
Message-ID: <CB3F493E.1A354%anthony.ballo_at_onecall.com>
Anyone running SOA 11g out there? Well, we just started working on a data purge strategy (PS4) using the Looped Purge script supplied by Oracle. Like others have written on the web, it doesn't delete everything so I put together a "manual purge" script that deletes the data and then does a SHRINK and DEALLOCATE UNUSED on various tables.
My question is this: We have purged about 90% of the data in our tablespace and would now like to recover (shrink) the datafile(s) using:
ALTER DATABASE DATAFILE '+DG1/tstsoa/datafile/dev_soainfra.310.767790105'
RESIZE 5G;
commit;
But then receive this:
ERROR:
ORA-03297: file contains used data beyond requested RESIZE value
Is there now a way to "coalesce" the data stored in the datafile so it is not fragmented? Or, is my only option to create a new tablespace and move all the Objects (tables and indexes) to the new - then rebuild (drop datafiles) and resize the Original tablespace and move the Objects back?
Thanks,
Anthony
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 20 2012 - 19:03:36 CST