Re: bytes per extent

On Fri, Apr 12, 2002 at 12:13:25PM -0800, Browett, Darren wrote:
> I am assumming the table is contained within one extent.
>
> If you did an analyze table, wouldn't that show the amount of data (num_rows
> * avg_row_len)
> within the table (extent) which could be used to monitor growth.

Yep, but I don't want an estimate, I want the byte count.

great metalink notes I found:

Note:116565.1

Note:116923.1

> -----Original Message-----
> Sent: April 12, 2002 10:04 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
>
> There are lots of versions of this around, but this reports
> the sum of the allocated extents. My problem is that the
> creater of this db made a huge extent and I don't seem to
> have a way to track growth within the extent.
>
>
>
>
> On Thu, Apr 11, 2002 at 07:13:31AM -0800, Ruth Gramolini wrote:
> > Here is a script that I got from another lister. Hope it is what you're
> > looking for.
> >
> > column tsname format a25 heading 'Tablespace Name'
> > column tot format 99,999,999 heading 'Size (K)'
> > column fsp Format 99,999,999 heading 'Free (K)'
> > column csp Format 999,999 heading 'Free|Extents'
> > column msp Format 9,999,999 heading 'Max Free|Ext (K)'
> > column pctused Format 999.99 heading '% Used'
> >
> > column tsno noprint
> > compute sum of tot fsp on report
> > break on report
> >
> > select
> > fi.tablespace_name "tsname",
> > sum(fi.bytes)/1024 "tot",
> > iv.free/1024 "fsp",
> > ((SUM(fi.bytes)-iv.free)/SUM(fi.bytes))*100 "pctused",
> > iv.no_of_exts "csp",
> > iv.max/1024 "msp"
> > from
> > dba_data_files fi,
> > (
> > select
> > t.tablespace_name,
> > NVL(MAX(f.bytes),0) max,
> > NVL(sum(f.bytes),0) free,
> > count(f.bytes) no_of_exts
> > from
> > sys.dba_free_space f,
> > sys.dba_tablespaces t
> > where
> > t.tablespace_name=f.tablespace_name(+)
> > and t.status != 'INVALID'
> > group by
> > t.tablespace_name
> > ) iv
> > where
> > fi.tablespace_name = iv.tablespace_name
> > GROUP BY
> > fi.tablespace_name,
> > iv.free,
> > iv.no_of_exts,
> > iv.max
> > ORDER BY
> > fi.tablespace_name
> > /
> >
> > clear breaks
> > clear columns
> > clear computes
> >
> >
> > Regards,
> > Ruth
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Thursday, April 11, 2002 10:39 AM
> >
> >
> > > Is there a query to get the number of bytes used and free
> > > in an each extent?
> > > ===============================================================
> > > Ray Stell stellr_at_vt.edu (540) 231-4109 KE4TJC 28^D
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Ray Stell
> > > INET: stellr_at_cns.vt.edu
> > >
> > > 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: Ruth Gramolini
> > INET: rgramolini_at_tax.state.vt.us
> >
> > 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).
>
> --
> ===============================================================
> Ray Stell stellr_at_vt.edu (540) 231-4109 KE4TJC 28^D
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Ray Stell
> INET: stellr_at_cns.vt.edu
>
> 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: Browett, Darren
> INET: dbrowett_at_city.coquitlam.bc.ca
>
> 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).

-- 
===============================================================
Ray Stell   stellr_at_vt.edu     (540) 231-4109     KE4TJC    28^D
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ray Stell
  INET: stellr_at_cns.vt.edu

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).