Re: shrinking space

From: Nuno Souto <>
Date: Wed, 01 Jun 2011 22:24:34 +1000
Message-ID: <>

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

> For that, Iíll direct you to asktom:

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


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.

Nuno Souto
in rainy Sydney, Australia

Received on Wed Jun 01 2011 - 07:24:34 CDT

Original text of this message