Re: "Deallocate Unused" not releasing space above HWM
- From: Mladen Gogala <mladen@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 11 Mar 2004 11:51:34 -0500
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@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mladen Gogala
> Sent: Wednesday, March 10, 2004 11:39 PM
> To: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
