About the dbms_space.free_blocks. This one tells you
the free blocks below HWM. However it is based on the
value of PCTUSED.
Read the notes 116565.1 and the 77635.1 from metalink.
Regards.
- Chuan Zhang <chuan_at_asiaonline.net> wrote:
> 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).
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).
Received on Fri Jul 13 2001 - 04:36:10 CDT
