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: vidya kalyanaraman <>
Date: Thu, 11 Mar 2004 11:47:22 +0530
Message-ID: <>

Hi Tim
 Thanks for the detail explanation. I was making mistake in understading "never been used" blocks and 'deallocate unused"


Tim Gorman wrote:

>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
>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
>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
> *
> * Description:
> * SQL*Plus script to display amount of unused space in
> * datafiles belonging to a particular tablespace. The
> * value for "highest_mb" is the lowest value you can "shrink"
> * the datafile to...
> *
> * Please note that the V_BLK_DIV substitution variable assumes
> * an 8Kb block size for the tablespace in question. Please
> * change accordingly if the blocksize of the database/tablespace
> * is not 8Kb...
> *
> * Modifications:
> *********************************************************************/
>set pagesize 100 linesize 100 trimout on trimspool on
>set feedback off verify off echo off feedback off timing off
>col file_name format a50
>col sum_mb format 99,990.00
>col highest_mb format 9,990.00
>col tot_mb format 9,990.00
>/* "#-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,
> f.blocks/128
>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
>>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
>>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
>>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
>>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
>>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


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

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 Thu Mar 11 2004 - 00:15:58 CST

Original text of this message