| 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
LOL
Maybe I should have included a note about the diagram referenced in the
quote.
In this case Left = Below and Right = Above
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mladen Gogala
Sent: Thursday, March 11, 2004 11:52 AM
To: oracle-l_at_freelists.org
Subject: Re: "Deallocate Unused" not releasing space above HWM
Left and right of HWM? For goodness sake, it's an oracle variable, not the iron curtain! Let's not lose our sense of direction over this, OK?
On 03/11/2004 10:40:41 AM, "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.
> --
> 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
> -----------------------------------------------------------------
>
-- 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 -----------------------------------------------------------------Received on Thu Mar 11 2004 - 16:25:11 CST
 |
 |