Re: "Deallocate Unused" not releasing space above HWM

Thanks Mladen. Thanks for the wonderful explanation.

You are one of the best in this list :-)

Regards
Vidya

Mladen Gogala wrote:

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

-- 

Vidya Kalyanaraman
Phone: +91 80 5108 4285
Fax:+91 80 5506749

Principal Consultant
iTech Practice

Oracle Solution Services India
PBC software Park
Level 1 and 2
No. 9 Hosur Road
Bangalore - 560 029
India
-----------------------------------



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