rem tf.sql - report free tablespace
rem
rem Cary Millsap, Oracle Corporation
rem Copyright (c) 1990,1992 by Oracle Corporation
rem @(#)tf.sql  1.2 (93/11/07)

def aps_prog    = 'tf.sql'
def aps_title   = 'Database Freespace Summary'
start apstitle

col tsname  format         a12 justify c heading 'Tablespace'
col nfrags  format       9,990 justify c heading 'Free|Frags'
col mxfrag  format 999,999,990 justify c heading 'Largest|Frag (KB)'
col totsiz  format 999,999,990 justify c heading 'Total|(KB)'
col avasiz  format 999,999,990 justify c heading 'Available|(KB)'
col dtasiz  format 999,999,990 justify c heading 'SpaceUsed|(KB)'
col pctusd  format         990 justify c heading 'Pct|Used'

break on report
compute sum of totsiz dtasiz avasiz on report

select
  total.tablespace_name                       tsname,
  count(free.bytes)                           nfrags,
  nvl(max(free.bytes)/1024,0)                 mxfrag,
  total.bytes/1024                            totsiz,
  nvl(sum(free.bytes)/1024,0)                 avasiz,
  ((total.bytes/1024) - (nvl(sum(free.bytes)/1024,0)))  dtasiz,
  (1-nvl(sum(free.bytes),0)/total.bytes)*100  pctusd
from
  aps_data_files  total,
  dba_free_space  free
where
  total.tablespace_name = free.tablespace_name(+)
group by
  total.tablespace_name,
  total.bytes;

