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;