Re: "Deallocate Unused" not releasing space above HWM
- From: Mladen Gogala <mgogala@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 11 Mar 2004 00:45:48 -0500
It's late, my telepathic abilities are somewhat lower then usual and
I failed to read Tim's mind. [A]bort,[R]etry,[I]gnore?
On 03/11/2004 12:26:37 AM, k.sriramkumar@xxxxxxxxxxxxxxxxxx wrote:
> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of Mladen Gogala
> Sent: Thursday, March 11, 2004 10:40 AM
> To: oracle-l@xxxxxxxxxxxxx
> 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@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
> > -----------------------------------------------------------------
> >
>
> --
> Mladen Gogala
> Oracle DBA
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
--
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
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:
