Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: "Deallocate Unused" not releasing space above HWM

Re: "Deallocate Unused" not releasing space above HWM

From: Adam Donahue <>
Date: Thu, 11 Mar 2004 12:32:38 -0800
Message-ID: <>

Is there a case where you can deallocate unused for a table other than when you've manually allocated additional extents?


Powell, Mark D wrote:

>I disagree with the definition given here. Space below the HWM has been
>used to hold data while space above the HWM has been allocated to the object
>but has never been used. The alter table deallocate command will only
>release space above the HWM.
>From the Concepts Manual (ver 9.2) definition of "High Water Mark":
>The high water mark is the boundary between used and unused space in a
>segment. As requests for new free blocks that cannot be satisfied by
>existing free lists are received, the block to which the high water mark
>points becomes a used block, and the high water mark is advanced to the next
>block. In other words, the segment space to the left of the high water mark
>is used, and the space to the right of it is unused.
>-- Mark --
>-----Original Message-----
>[]On Behalf Of Mladen Gogala
>Sent: Wednesday, March 10, 2004 11:39 PM
>Subject: Re: "Deallocate Unused" not releasing space above HWM
>On 03/10/2004 11:25:30 PM, vidya kalyanaraman wrote:
>> Thanks for the reply. But I was under the impression that "deallocate
>>unused" is supposed to clear off the "never been used blocks" (which is
>>rep as empty_blocks), which is above the HWM.
>>Am I making any mistake here?
>Yes you are. Blocks above HWM have been used, otherwise they wouldn't be
>above the watermark. Watermark points to the row after the last inserted
>row. What is the function of watermark, anyway? Watermark is here to for
>oracle to know
> a) where can it start with direct insert.
> b) how many blocks must be read during the full table scan.
>So, location of the HWM is recorded in the table header. As you insert rows
>in the table,
>the HWM is moved. So, the only way a block can be above watermark is if it
>was used.
>You can allocate unused block with alter table emp allocate extent size
>That will create a "never been kissed" extent, 32G in size. Alter table
>deallocate unused
>will rid you of those 32G.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Thu Mar 11 2004 - 17:25:05 CST

Original text of this message