set line 130
set pages 1000
col dummy noprint
col tablespace for a20             head 'Tablespace'
col kbytes     for 999,999,999,999 head 'Kbytes_alloc'
col used       for 999,999,999,999 head 'Used'
col free       for 999,999,999,999 head 'Free'
col pctused    for 999.99          head 'Pct_used'
col largest    for 999,999,999,999 head 'Largest'
break on report
compute sum of kbytes    on report
compute sum of free      on report
compute sum of used      on report
select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKNOWN')) tablespace,
       kbytes_alloc kbytes,
       kbytes_alloc-nvl(kbytes_free,0) used,
       nvl(kbytes_free,0) free,
       nvl(largest,0) largest,
       ((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pctused
                  from (select sum(bytes)/1024 kbytes_free,
                         max(bytes)/1024 largest,
                         tablespace_name
                             from dba_free_space
                             group by tablespace_name) a,
                 (select sum(bytes)/1024 kbytes_alloc,
                         tablespace_name
                             from dba_data_files
                             group by tablespace_name) b
            where a.tablespace_name(+)=b.tablespace_name
        order by pctused;

