Re: tablespace management

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Mon, 11 Jan 2010 07:36:50 -0800 (PST)
Message-ID: <2c87a48d-6bea-4a1e-a7a0-fe182624e189_at_c34g2000yqn.googlegroups.com>

On Jan 11, 9:45 am, ddf <orat..._at_msn.com> wrote:
> On Jan 11, 8:55 am, dba cjb <chris.br..._at_providentinsurance.co.uk>
> wrote:
>
> > working in 10.2.0.4 enterpise on windows nt / have truncated many
> > objects from a tablespace...rman backups reduced by 400g but can't
> > shrink any datafiles..only 2 objects left in ts
>
> > Please could you advise on options to free up physical space used / do
> > i have to move the 2 objects or could i rebuild in situ
>
> > regards
> >  Chris B
>
> You will need to move the tables to a new, smaller tablespace to
> reclaim the storage as truncate does not reset the HWM for the
> datafile, only the table.  Once moved you can drop the old tablespace
> including contents and datafiles, and reduce the disk footprint.
>
> David Fitzjarrell

Or if you intend to keep the same tablespace name and datafiles you could try re-organinzing the remaining objects with the tablespace, which should migrate the extents in use toward the logical beginning of the files. You can then release space at the logical end of the files.

For most tables where LONG and LONG RAW columns are not in use you can use alter table move and atler index rebuild to migrate the objects toward the front of the file. Otherwise export, drop or truncate, and import can be used.

HTH -- Mark D Powell -- Received on Mon Jan 11 2010 - 09:36:50 CST