RE: "Deallocate Unused" not releasing space above HWM

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mladen Gogala Sent: Wednesday, March 10, 2004 11:39 PM To: oracle-l_at_freelists.org
Subject: Re: "Deallocate Unused" not releasing space above HWM

On 03/10/2004 11:25:30 PM, vidya kalyanaraman wrote:
> Mladen
> 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

1. where can it start with direct insert.
2. 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.

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------