rem   name       : FREE2.sql 
rem   date       : 01-01-1998
rem   description: Shows free space for each tablespace (size and percent).
rem   compatible : v7.1 upwards
col name     format A16         head "Tablespace Name"
col pct_used format 999.9       head "Pct|Used"
col Kbytes   format 999,999,999 head "KBytes"
col used     format 999,999,999 head "Used"
col free     format 999,999,999 head "Free"
col max_free format 999,999,999 head "Max size|free chunk"
break   on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report

select   nvl(FULL.tablespace_name,nvl(FREE.tablespace_name,'UNKOWN')) Name
        ,kbytes_used                    Kbytes
        ,kbytes_used-nvl(kbytes_free,0) Used
        ,nvl(kbytes_free,0)             Free
        ,((kbytes_used-nvl(kbytes_free,0)) / kbytes_used)*100 Pct_used
        ,nvl(max_free,0)                Max_free
from 
     ( select  sum(bytes)/1024    Kbytes_free
              ,max(bytes)/1024    max_free
              ,tablespace_name
       from    sys.DBA_FREE_SPACE
       group by tablespace_name ) FREE,
     ( select  sum(bytes)/1024    Kbytes_used
              ,tablespace_name
       from    sys.DBA_DATA_FILES
       group by tablespace_name ) FULL
where  FREE.tablespace_name (+) = FULL.tablespace_name
/
