--
--  tablespaces_freespace_show.sql
--
--  Tablespaces - Displays free space for all tablespaces
--
--  Compatability: 7.3.x and above
--
--  Usage: @tablespaces_freespace_show
--
--
-- save SQL*Plus settings:
store set savesqlplus_settings.sql replace

-- set SQL*Plus settings:
set linesize 133 pages 40

col name     format A30         heading tablespace
col pct_used format 999.99      heading "Pct|Used"
col Mbytes   format 999,999,999 heading "Mbytes"
col used     format 999,999,999 heading "MB Used"
col free     format 999,999,999 heading "MB Free"
col max_free format 999,999,999 heading "Max size|free chunk"

break   on report
compute sum of Mbytes on report
compute sum of free on report
compute sum of used on report

select  nvl(ts.tablespace_name,nvl(ts_free.tablespace_name,'UNKOWN')) Name
        ,Mbytes_used                    Mbytes
        ,Mbytes_used-nvl(Mbytes_free,0) Used
        ,nvl(Mbytes_free,0)             Free
        ,((Mbytes_used-nvl(Mbytes_free,0)) / Mbytes_used)*100 Pct_used
        ,nvl(max_free,0)                Max_free
from 
     ( select  sum(bytes)/1024/1024   Mbytes_free
              ,max(bytes)/1024/1024   max_free
              ,tablespace_name
       from    sys.DBA_FREE_SPACE
       group by tablespace_name ) ts_free,
     ( select  sum(bytes)/1024/1024   Mbytes_used
              ,tablespace_name
       from    sys.DBA_DATA_FILES
       group by tablespace_name ) ts
where  ts_free.tablespace_name (+) = ts.tablespace_name
order by  name
;

--
-- restore SQL*Plus settings:
@savesqlplus_settings