Re: bytes per extent

From: Ruth Gramolini <rgramolini_at_tax.state.vt.us>
Date: Thu, 11 Apr 2002 07:13:31 -0800
Message-ID: <F001.00441CF4.20020411071331@fatcity.com>

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

Received on Thu Apr 11 2002 - 10:13:31 CDT