Thanks for the response Diego. I will try running this
on my system.
Rgds
Deepak
- Diego Cutrone <dcutrone_at_afip.gov.ar> wrote:
> Deepak:
> As a matter of fact I think you can find how
> many blocks below HWM
> are unused (have no rows at all), by counting the
> number of blocks that are
> below the HWM and hold at least one row. (check my
> previous mail)
>
> SELECT COUNT(DISTINCT
> SUBSTR(rowid,15,4)||SUBSTR(rowid,1,8)) FROM TABLE;
>
> This query will count how many blocks have data (and
> of course) are below
> HWM.
> I know this is not a perfect calclulation, because
> it can report 10 blocks
> used and these blocks could only have 1 row each,
> but it's better than the HWM value or than the
> dbms_space.free_blocks, I
> think.
>
> Please correct me if I'm wrong...
>
> Greetings
> DC
>
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> Sent: Thursday, January 17, 2002 9:05 PM
>
>
> > Hi Do,
> >
> > Here is the breakup for a space usage for a
> segment:
> >
> > 1. Allocated size (use dba_segments)
> >
> > 2 Used Blocks in segments(use dba_tables.blocks)
> > --> Truly Used ( ??)
> > --> Free Blocks (??)
> >
> > 3. Unused Blocks (use dba_tables.empty_blocks)
> >
> > the caveat i guess is in step 2. The used block
> number
> > is based on the high watermark. which means that
> the
> > number you will get for point 2. above will also
> > include "space that contains no rows" becuz its
> below
> > the high water mark.
> >
> > Afaik, there is'nt a way to find these "free
> blocks"
> > below the high water mark.. unless you re-build
> table
> > and then get the value from dba_tables.blocks ..
> >
> > feel free to correct me ..
> >
> > Deepak
> >
> > > -----Original Message-----
> > > Sent: Thursday, January 17, 2002 12:12 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > hi guys!
> > >
> > > what would be the easiest way to compute the
> space
> > > in bytes used by a segment?
> > > it is rather easy to determine the # of used
> blocks
> > > by a specific segment by looking at the
> dba_extents
> > > for example. but how many blocks have been
> really
> > > used
> > > in one of those extents. i guess i would be able
> > > to compute the free blocks by using the
> dbms_space
> > > package but it just seems to complicated.
> > > any hints ... ?
> > >
> > > thank you
> > >
> > > -do
> > >
> > >
> > >
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Send FREE video emails in Yahoo! Mail!
> > http://promo.yahoo.com/videomail/
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > --
> > Author: Deepak Thapliyal
> > INET: deepakthapliyal_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: Diego Cutrone
> INET: dcutrone_at_afip.gov.ar
>
> 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).
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deepak Thapliyal
INET: deepakthapliyal_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 Jan 18 2002 - 16:12:24 CST
