Re: shrinking space
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-lReceived on Wed Jun 01 2011 - 07:24:34 CDT