Hi anthony, I believe you can enable row movement on the tables then use alter table shrink space /shrink space compact/shrink space cascade to "free up" the unused space. Anthony Sanchez On Jan 20, 2012, at 18:03, Anthony Ballo <anthony.ballo@xxxxxxxxxxx> wrote: > 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 > > > -- > //www.freelists.org/webpage/oracle-l > > -- //www.freelists.org/webpage/oracle-l