RE: bytes per extent

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.

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