Re: "Deallocate Unused" not releasing space above HWM

  • From: vidya kalyanaraman <vidya.kalyanaraman@xxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 11 Mar 2004 11:47:22 +0530

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,

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, k.sriramkumar@xxxxxxxxxxxxxxxxxx at
k.sriramkumar@xxxxxxxxxxxxxxxxxx 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: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of vidya kalyanaraman Sent: Wednesday, March 10, 2004 5:58 PM To: oracle-l@xxxxxxxxxxxxx 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: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

---------------------------------------------------------------- Please see the official ORACLE-L FAQ: ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx 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

Other related posts: