Re: "Deallocate Unused" not releasing space above HWM

  • From: Adam Donahue <adonahue@xxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 11 Mar 2004 12:32:38 -0800

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-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mladen Gogala
Sent: Wednesday, March 10, 2004 11:39 PM
To: oracle-l@xxxxxxxxxxxxx
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
32768M; 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: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at FAQ is at -----------------------------------------------------------------

Other related posts: