Re: "Deallocate Unused" not releasing space above HWM
- From: Tim Gorman <tim@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 11 Mar 2004 06:06:58 -0700
Yeah, I always get those confused... :-)
Sorry!
on 3/10/04 9:59 PM, k.sriramkumar@xxxxxxxxxxxxxxxxxx at
k.sriramkumar@xxxxxxxxxxxxxxxxxx wrote:
> Hi Tim,
>
> Thanks for the clarity. I have one more doubt. I am unable to use this
> command "ALTER DATABASE DATAFILE ... DEALLOCATE UNUSED"...I believe you are
> referring to resize option of the Datafile?
>
> Best Regards
>
> Sriram Kumar
>
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of Tim Gorman
> Sent: Wednesday, March 10, 2004 7:17 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: "Deallocate Unused" not releasing space above HWM
>
> Vidya,
>
> 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...
>
> <snip>
> /**********************************************************************
> * 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
> </snip>
>
> It would not be difficult to turn this report into a "SQL-generating-SQL"
> script, if you wished...
>
> Hope this helps...
>
> -Tim
>
>
>
> 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
>> 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
>>
>>
>>
>>
>>
>> DISCLAIMER:
>> 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: 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
> -----------------------------------------------------------------
>
>
> DISCLAIMER:
> 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: 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:
