Oracle : Script to Report On Space Usage in Tablespaces

set pagesize 300
 set linesize 120
 column sumb format 9,999,999,999,999
 column extents format 999999
 column bytes format 9,999,999,999,999
 column largest format 9,999,999,999,999
 column Tot_Size format 9,999,999,999,999
 column Tot_Free format 9,999,999,999,999
 column Pct_Free format 9,999,999,999,999
 column Chunks_Free format 9,999,999,999,999  
 column Max_Free format 9,999,999,999,999
 set echo off
 spool TFSTSNFO.SQL
       
 PROMPT  SPACE AVAILABLE IN TABLESPACES  
       
 select a.tablespace_name,sum(a.tots) Tot_Size,  
 sum(a.sumb) Tot_Free,
 sum(a.sumb)*100/sum(a.tots) Pct_Free,  
 sum(a.largest) Max_Free,sum(a.chunks) Chunks_Free  
 from  
 (
 select tablespace_name,0 tots,sum(bytes) sumb,  
 max(bytes) largest,count(*) chunks
 from dba_free_space a
 group by tablespace_name
 union
 select tablespace_name,sum(bytes) tots,0,0,0 from
 dba_data_files
 group by tablespace_name) a
 group by a.tablespace_name;
       
 column owner format a15
 column segment_name format a30
       
       
 PROMPT   SEGMENTS WITH MORE THAN 20 EXTENTS  
       
 select owner,segment_name,extents,bytes ,  
 max_extents,next_extent  
 from  dba_segments  
 where segment_type in ('TABLE','INDEX') and extents>20  
 order by owner,segment_name;
       
       
       
 PROMPT SEGMENTS WHERE THERE'S NOT ENOUGH ROOM FOR THE NEXT EXTENT  

 select  a.owner, a.segment_name, b.tablespace_name,
      decode(ext.extents,1,b.next_extent,
      a.bytes*(1+b.pct_increase/100)) nextext,  
      freesp.largest
 from    dba_extents a,
      dba_segments b,
      (select owner, segment_name, max(extent_id) extent_id,
      count(*) extents  
      from dba_extents  
      group by owner, segment_name
      ) ext,
      (select tablespace_name, max(bytes) largest
      from dba_free_space  
      group by tablespace_name
      ) freesp
 where   a.owner=b.owner and
      a.segment_name=b.segment_name and
      a.owner=ext.owner and  
      a.segment_name=ext.segment_name and
      a.extent_id=ext.extent_id and
      b.tablespace_name = freesp.tablespace_name and  
      decode(ext.extents,1,b.next_extent,
      a.bytes*(1+b.pct_increase/100)) > freesp.largest
 /
spool off