##  DBA Command Post - extents.sql


set pages    50000 
column owner           format a12    heading 'OWNER'
column segment_type    format a5     heading 'TYPE'
column tablespace_name format a12    heading 'TABLESPACE'
column segment_name    format a28    heading 'NAME'
column max_extents     format 999    heading 'MAX'
column extents         format 999    heading 'EXTS'
column pct             format 999    heading '%'
column Error	       format a4     heading 'FIX'

break on owner skip 1 on tablespace_name on segment_type 

select  owner,
        tablespace_name,
        segment_type,
        segment_name,
        max_extents,
        extents,
        extents/max_extents*100 PCT,
        decode(sign(75 - (extents/max_extents*100)), -1, '***',
        decode(sign(20 - extents), -1, '****', '')) Error
  from  sys.dba_segments
 where  extents       > 1  
   and  segment_type != 'ROLLBACK'
   and  segment_type != 'CACHE'
   and  owner        != 'SYS'
 order  
     by owner.
        tablespace_name,
        segment_type
/