Re: shrinking space

From: Nuno Souto <dbvision_at_iinet.net.au>
Date: Wed, 01 Jun 2011 22:24:34 +1000
Message-ID: <4DE62F82.40902_at_iinet.net.au>



Joel Slowik wrote,on my timestamp of 1/06/2011 9:45 PM:

> For that, Iíll direct you to asktom:
> http://asktom.oracle.com/pls/asktom/f?p=100:11:4000442744825987::::P11_QUESTION_ID:153612348067

One thing about datafile shrinking: if a "stray" extent near the end of the file stops one from shrinking the file size, asktom says it can't be fixed without a major reorg.
It can, assuming there is enough free space somewhere else in the datafile(s). The command

ALTER TABLE <tname> MOVE TABLESPACE <tsname>

where <tname> is the table name and <tsname> the *same tablespace* will compact all such stray extents back to the first free space(s) in the datafile(s) making up that tablespace. I think MOVE is available in 9i - although the online variant of it may not be? Of course: you need to be logged on as the table owner.

At work I've got a SQL query that will show you which tables have extents near the end of any given datafile: you can use that to determine which tables to move/recompact within the same tablespace. If you need it let me know and I'll post.

It's how I manage to never need to fully reorganize my tablespace(s): I just move fragmented tables back into available contiguous free "slots" in the *same* ts.
;)

-- 
Cheers
Nuno Souto
in rainy Sydney, Australia
dbvision_at_iinet.net.au

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jun 01 2011 - 07:24:34 CDT

Original text of this message