Re: ORA-03297 after PURGE and wanting to resize datafiles

From: anthony sanchez <anthonycsanchez_at_gmail.com>
Date: Fri, 20 Jan 2012 18:51:51 -0700
Message-Id: <9647F470-B0BC-4C11-89EE-262FCABB9ECA_at_gmail.com>

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_at_onecall.com> 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 20 2012 - 19:51:51 CST