Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: "Deallocate Unused" not releasing space above HWM

Re: "Deallocate Unused" not releasing space above HWM

From: Tim Gorman <>
Date: Wed, 10 Mar 2004 06:47:23 -0700
Message-ID: <>


Sriram is correct. The act of deletion means that insertions had previously occurred, meaning that a segment's "high-water mark" had to have been moved to accommodate the insertion.

In fact, the BLOCKS and EMPTY_BLOCKS values that you have been looking at have nothing to do with the DEALLOCATE UNUSED command. Any blocks currently belonging to a segment have been "used", by definition. Any blocks previously used by segments that have been dropped or truncated are also still recognizable as "used", I believe (someone correct me on that?).

There is confusion when using the phrase "high-water mark". Deletions and insertions involve the HWM maintained within a segment. That is what the phrase HWM truly refers to, in Oracle. In contrast, the "DEALLOCATE UNUSED" command involves the concept of an illusory "HWM" implied within a datafile.

When a datafile is created, the blocks within are initially "unformatted". I believe that they are "initialized" with some pattern, but this initial pattern is referred to as "unformatted". They are recognizable by Oracle as never having been "used".

As you allocate extents within the datafile, the headers of the blocks within the new extent are updated to reflect that fact (i.e. Oracle version, object_type, object_id, etc). Now, the blocks are recognizable by Oracle as "used"...

Unlike the HWM involved in space-management within segments, this "high-water mark" within datafiles is only implied, and is not maintained as such anywhere. New extents are allocated within the datafile according to the rules of a dictionary-managed or a locally-managed tablespace, whichever applies. The illusion of a "high-water mark" within datafiles is provided by the way the datafiles tend to "fill in" from the bottom up for most algorithms.

The ALTER DATABASE DATAFILE ... DEALLOCATE UNUSED command attempts to shrink the datafile from very "end" of the file, heading "inwards". From an OS standpoint, it is reducing the "length" of the file. It does not "coalesce" the datafile to "squeeze out" all of the unused blocks, but it simply reduces the length of the datafile by however many unused blocks are clustered at the "end" of the file. That is where they are usually clustered, anyway...

I wrote a SQL*Plus script a while ago that plays on the assumption that DEALLOCATE UNUSED works from the end of the file "inward" and experience has borne out that assumption, since subsequent DEALLOCATE UNUSED commands have worked according to the way the report stated. I was surprised to find that I have not yet posted the script, named "dealloc_unused.sql", to my website (I'll get that done soon); here it is...



* File: dealloc_unused.sql
* Type: SQL*Plus script
* Author: Tim Gorman (Evergreen Database Technologies, Inc.)
* Date: 05-Jan-99

/* "#-of-blocks" divided by V_BLK_DIV equals Mbytes */ define V_BLK_DIV=128 /* value of 128 implies DB_BLOCK_SIZE = 8192 */ define V_TS=&&1 /* name of tablespace to report upon */

select f.file_name,

        sum(nvl(e.blocks,0))/128 sum_mb,
        max(nvl(e.block_id,0)+nvl(e.blocks,0))/128 highest_mb,
        f.blocks/128 tot_mb
from    dba_extents e,
        dba_data_files f
where   f.tablespace_name = upper('&&V_TS')
and     e.tablespace_name (+) = f.tablespace_name
and     e.file_id (+) = f.file_id
group by f.file_name,

spool dealloc_unused_&&V_TS
spool off
set verify on linesize 80
undef V_TS
undef V_BLK_DIV

It would not be difficult to turn this report into a "SQL-generating-SQL" script, if you wished...

Hope this helps...


on 3/10/04 5:48 AM, at wrote:

> Hi Vidya,
> I thought Deletes happen within the HWM....I am confused by your statement
> "deletes above the HWM".....Guru's pls correct me if I am wrong.
> Best Regards
> Sriram Kumar
> -----Original Message-----
> From: [] On
> Behalf Of vidya kalyanaraman
> Sent: Wednesday, March 10, 2004 5:58 PM
> To:
> Subject: Re: "Deallocate Unused" not releasing space above HWM
> Hi All
> I am trying to reclaim the wasted space (huge deletes), which are above the
> HWM.  I had analysed the table before and got the "empty_blocks" details from
> dba_tables.
> I am using the following query,
> alter table tab1 deallocate unused;
> and then to bring down the HWM,
> alter table tab1 move tablespace AR_DATA;  ---- Moved within the same
> tablespace
> and then I had rebuilt all the corresponding indexes.
> As a last step, I had coalesced the tablespace.
> Next day (because SMON is not going to clear it up immediately), I had
> analyzed the tables again and got the "empty_blocks" details from dba_tables.
> When I look the empty_blocks, for some tables it has not yet released the
> space.  
> Am I missing  any steps?  I searched Metalink, but it looks like I have
> covered everything that needs to be.
> I have one more question. To really reclaim the space, do I have to move the
> table out of its own tablespace ?
> Any thoughts are most welcome.
> Thanks and Regards
> Vidya
> This message contains privileged and confidential information and is intended
> only for the individual named.If you are not the intended recipient you should
> not disseminate,distribute,store,print, copy or deliver this message.Please
> notify the sender immediately by e-mail if you have received this e-mail by
> mistake and delete this e-mail from your system.E-mail transmission cannot be
> guaranteed to be secure or error-free as information could be
> intercepted,corrupted,lost,destroyed,arrive late or incomplete or contain
> viruses.The sender therefore does not accept liability for any errors or
> omissions in the contents of this message which arise as a result of e-mail
> transmission. If verification is required please request a hard-copy version.
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ:
> ----------------------------------------------------------------
> To unsubscribe send email to:
> put 'unsubscribe' in the subject line.
> --
> Archives are at
> FAQ is at
> -----------------------------------------------------------------

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Wed Mar 10 2004 - 07:48:19 CST

Original text of this message