Re: find free space under HWM for a table

Thanks for all your reply.

I am still confused about getting free space or blocks under HWM for a table.

Let me first list something here for clarification. If something wrong, please correct it.

1. dbms_space:

UNUSED_SPACE Procedure Returns information about unused space in an object. To my understanding, these unused space could be both above and under HWM.

FREE_BLOCKS Procedure Returns information about free blocks in an object. Again, these free blocks include blocks above and under HWM.

>From this dbms_space package, you cannot simply get the free space or blcoks
under HWM.

2. dba_segments

blocks: Size, in Oracle blocks, of the segment.

3. dba_tables (analyzed)
blocks: Number of used data blocks in the table empty_blocks: Number of empty (never used) data blocks in the table. It seems above the HWM.

>From the above, you cannot get the free blocks under HWM. I define the "free
blocks" as " blocks are blocks which are never used or once used but later released because of mass deleting"

Much appreciated for your reply.
Thanks,

Chuan

• Original Message ----- To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Thursday, July 12, 2001 6:55 PM

> REMEMBER that empty_blocks represent the amount of
> blocks above the HWM. The only way to know the blocks
> below the HWM is with this select:
>
>
> on 7.X select count(distinct
> substr(rowid,15,4)||substr(rowid,1,8)) from
> &owner..&table_name.;
>
> on 8.X select count( distinct substr(rowid,1,15)) from
> &owner..&table_name.;
>
> This gives you the real space occupy by a segment or
> as I said the amount of blocks below the HWM. Then
> with the db_block_size you can infer the value in
> bytes.
>
> Regards.
>
> --- Vladimir Begun <jester_at_whale.sunbay.crimea.ua>
> wrote:
> > on Jul 11, 2001 at 11:30:18PM, Chuan Zhang wrote:
> > > Hi, DBA gurus,
> > >
> > > Recently, I will archiving some big tables. How
> > to find free space under
> > > HWM for a table?
> >
> > Do analyze and then you can calculate it:
> >
> > dba_segments.blocks - dba_tables.empty_blocks - 1
> >
> > Be aware the blocks which are below HWM can be empty
> > because of preallocation or delete activities.
> >
> > --
> > Vladimir Begun | echo
> > "Congratulations. You aren't running
> > http://vbegun.net/ | Eunice."
> > http://vbegun.net/wap/ | -- Larry Wall
> > in Configure from
> > me_at_vbegun.net | the perl
> > distribution
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Vladimir Begun
> > INET: jester_at_whale.sunbay.crimea.ua
> >
> > Fat City Network Services -- (858) 538-5051 FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
> --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
> =====
> Eng. Christian Trassens
> Senior DBA
> Systems Engineer
> ctrassens_at_yahoo.com
> ctrassens_at_hotmail.com
> Phone : 541149816062
>
> __________________________________________________
> Do You Yahoo!?
> Get personalized email addresses from Yahoo! Mail
> http://personal.mail.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Christian Trassens
> INET: ctrassens_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Chuan Zhang
  INET: chuan_at_asiaonline.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).