| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: "Deallocate Unused" not releasing space above HWM
Is there a case where you can deallocate unused for a table other than
when you've manually allocated additional extents?
Adam
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_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
> 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.
>
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Mar 11 2004 - 17:25:05 CST
 |
 |