Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Please help ---- Oracle free spaces

Re: Please help ---- Oracle free spaces

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 22 Oct 2004 14:33:28 +1000
Message-ID: <41788d8f$0$10347$afc38c87@news.optusnet.com.au>


Someone wrote:

> I had tried out using the 'alter table X deallocate unused' commands, but
> why the result still the same? I really go mad with this!!!! Please
> help!!!!

You really *do* need to go and read that concepts manual.

"Unused" in the context of that command does not mean 'Now empty, therefore not in use'. It means 'Never, ever touched by a row, but nevertheless allocated to the table'.

Slightly more technically, it means: relinquish space above the high water mark. If there is no space above the high water mark, then the command achieves precisely nothing.

In other words, if you insert 1,000,000 rows, and that consumes 1000 blocks, the HWM is at the 1000th block (for the sake of argument). And those 1000 blocks happen (again for the sake of argument) to be organised into 10 100-block extents. Therefore, there is no 11th extent, with one or two blocks used up. Deallocate unused in this circumstance will not achieve anything: the HWM is at the end of the table, and there is no virgin space above it.

Insert a couple of more records, however. You cause the table to acquire an 11th extent (of 100 blocks). 99 of those blocks remain empty. Deallocate unused will return those 99 blocks to you, but the other 1001 blocks remain allocated.

Actually, if your tablespace is locally managed, not even that will happen, because you can't have odd-sized extents in uniform sized LMT, so the situation gets even more complicated. Frankly, de-allocate unused is rather an old-fashioned command, designed originally for dictionary-managed tablespace, and somewhat out of place in a world of LMTs.

If you want to pack a table in tightly after a lot of deletes, why not move it? Just issue the command 'alter table X move;' and see what happens.

HJR Received on Thu Oct 21 2004 - 23:33:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US