Prev: How you can save fuel and the environment
Next: Problem renaming a Tablespace Datafile in 10.2.0.4
From: dba cjb on 11 Jan 2010 08:55
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
From: ddf on 11 Jan 2010 09:45
On Jan 11, 8:55 am, dba cjb <chris.br...(a)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
From: Mark D Powell on 11 Jan 2010 10:36
On Jan 11, 9:45 am, ddf <orat...(a)msn.com> wrote:
> On Jan 11, 8:55 am, dba cjb <chris.br...(a)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 --

From: joel garry on 11 Jan 2010 14:10
On Jan 11, 7:36 am, Mark D Powell <Mark.Powe...(a)hp.com> wrote:
> On Jan 11, 9:45 am, ddf <orat...(a)msn.com> wrote:
>
>
>
> > On Jan 11, 8:55 am, dba cjb <chris.br...(a)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
--
@home.com is bogus.
http://hoopercharles.wordpress.com/2009/12/21/miscellaneous-metalink-performance-articles/

From: Robert Klemme on 11 Jan 2010 14:20

I'd also like to throw the consideration into the discussion that
shrinking a tablespace is only a reasonable thing to do if the TS stays
smaller permanently. If you are going to need the space again later you
should at least consider whether the exercise is worthwhile.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
 | 
Pages: 1
Prev: How you can save fuel and the environment
Next: Problem renaming a Tablespace Datafile in 10.2.0.4