Re: tablespace management
Date: Mon, 11 Jan 2010 11:10:15 -0800 (PST)
Message-ID: <3c788bcd-1977-4483-8dc7-4806e1b0a877_at_r5g2000yqb.googlegroups.com>
On Jan 11, 7:36 am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> 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.
I'm sure you meant physical?
>
> 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.
I seem to have forgotten all the intricacies of truncate, but I'm wondering from reviewing the docs if the minextents left after drop storage might be at the end of the file, if that's where they first were created?
This bit about NEXT seems conflicted:
From the 11.2 admin guide:
"The REUSE or DROP STORAGE option also applies to any associated indexes. When a table or cluster is truncated, all associated indexes are also truncated. The storage parameters for a truncated table, cluster, or associated indexes are not changed as a result of the truncation."
From the 11.2 SQL language reference:
"Specify DROP STORAGE to deallocate all space from the deleted rows from the table except the space allocated by the MINEXTENTS parameter of the table or cluster. This space can subsequently be used by other objects in the tablespace. Oracle Database also sets the NEXT storage parameter to the size of the last extent removed from the segment in the truncation process. This setting, which is the default, is useful for small and medium-sized objects. The extent management in locally managed tablespace is very fast in these cases, so there is no need to reserve space."
jg
-- _at_home.com is bogus. http://hoopercharles.wordpress.com/2009/12/21/miscellaneous-metalink-performance-articles/Received on Mon Jan 11 2010 - 13:10:15 CST