RE: "Deallocate Unused" not releasing space above HWM

Hi Mladen,

        Thank you for the input. I was referring to the command that was referred by Tim for releasing the storage at a Datafile level. I guess he was referring to resize option for the Datafile or a new command in 10g?

Best Regards

Sriram Kumar  

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Thursday, March 11, 2004 10:40 AM
To: oracle-l_at_freelists.org
Subject: Re: "Deallocate Unused" not releasing space above HWM

Nope. It is an "alter table option". Here is an example:

SQL> alter table emp deallocate unused;  

Table altered.  

Now, I saved immense quantities of disk space;

On 03/10/2004 11:59:33 PM, k.sriramkumar_at_iflexsolutions.com 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
> Sent: Wednesday, March 10, 2004 7:17 PM
> To: oracle-l_at_freelists.org
> 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_at_iflexsolutions.com at
> k.sriramkumar_at_iflexsolutions.com 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On
> > Behalf Of vidya kalyanaraman
> > Sent: Wednesday, March 10, 2004 5:58 PM
> > To: oracle-l_at_freelists.org
> > 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_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------