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" <[EMAIL PROTECTED]>
> 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 [EMAIL PROTECTED] (540) 231-4109 KE4TJC 28^D
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Ray Stell
> > INET: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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 [EMAIL PROTECTED] (540) 231-4109 KE4TJC 28^D
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ray Stell
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
