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
-----------------------------------------------------------------