set pages 1000 lines 132
col sz form 999,999
col free form 999,999
col used form 999,999
col pct_used form 999,990.00
col tablespace_name form a30
col autoextend_mb form 999,990.00
col smallest_free form 999,990.00
col average_free form 999,990.00
col largest_free form 999,990.00
col num_frags form 999,990

select  t.tablespace_name,
        d.mb sz, 
		d.autoextend_MB,
        s.mb used,
        d.autoextend_MB - s.mb free, 
		f.smallest smallest_free, 
		f.average average_free, 
		f.largest largest_free, 
		f.num num_frags
from sys.dba_tablespaces  t,
    (select tablespace_name,
            sum(bytes)/1024/1024 MB,
            min(bytes)/1024/1024 smallest,
            avg(bytes)/1024/1024 average,
            max(bytes)/1024/1024 largest,
            count(*) num
     from   sys.dba_free_space
     group by tablespace_name) f,
    (select tablespace_name,
            sum(bytes)/1024/1024 MB,
            sum(decode(maxbytes,0,bytes,maxbytes))/1024/1024 autoextend_MB
     from sys.dba_data_files
     group by tablespace_name) d,
    (select tablespace_name, sum(bytes)/1024/1024 MB
     from sys.dba_segments group by tablespace_name) s
where t.tablespace_name = f.tablespace_name (+)
and t.tablespace_name = d.tablespace_name (+)
and t.tablespace_name = s.tablespace_name (+) 
order by 5 desc, 1;