RE: Free space script

From: Shawn Ferris <Shawn.Ferris_at_twtelecom.com>
Date: Tue, 25 Jul 2000 08:53:55 -0600
Message-Id: <10569.112883@fatcity.com>

I use this one..

(It reports in K not in MB or Bytes.. Modify to suite your needs)

• freespace.sql 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
--- freespace.sql

HTH Received on Tue Jul 25 2000 - 09:53:55 CDT