doc

    Name:   exent.sql                        

    Author:  Mark Gurry


    The next output is not strictly tuning, but it gives you a list of all    
    database objects that will fail when they attempt to throw their next extent
    due to a lack of a free extent that is of sufficient size in the same
    tablespace as where the object resides. If the problem happens to occur 
    on a dictionary table, the whole database can potentially freeze, which I
    suppose is response related. 

#
set pagesize 999



spool exent.sql                            
ttitle 'Database Objects that will have Trouble Throwing Extents'

column owner format a10;
column segment_name format a22;
column segment_type format a10;
column tablespace_name format a14;
column next_extent format 999,999,999;

SELECT seg.owner, seg.segment_name,
       seg.segment_type, seg.tablespace_name,
       t.next_extent
   FROM sys.dba_segments seg,
        sys.dba_tables   t
WHERE  (seg.segment_type = 'TABLE'
  AND    seg.segment_name = t.table_name
  AND    seg.owner        = t.owner
  AND    NOT EXISTS
            (select tablespace_name
               from dba_free_space free
              where free.tablespace_name =  t.tablespace_name
                and bytes               >=  t.next_extent     ))
UNION
SELECT seg.owner, seg.segment_name,
       seg.segment_type, seg.tablespace_name,
       DECODE (seg.segment_type,
               'CLUSTER',  c.next_extent)
   FROM sys.dba_segments seg,
        sys.dba_clusters c 
WHERE   (seg.segment_type = 'CLUSTER'
  AND    seg.segment_name = c.cluster_name
  AND    seg.owner        = c.owner
  AND    NOT EXISTS
            (select tablespace_name
               from dba_free_space free
              where free.tablespace_name =  c.tablespace_name
                and bytes               >=  c.next_extent     ))
UNION
SELECT seg.owner, seg.segment_name,
       seg.segment_type, seg.tablespace_name,
       DECODE (seg.segment_type,
               'INDEX',    i.next_extent )
   FROM sys.dba_segments seg,
        sys.dba_indexes  i
WHERE  (seg.segment_type = 'INDEX'
  AND    seg.segment_name = i.index_name
  AND    seg.owner        = i.owner
  AND    NOT EXISTS
            (select tablespace_name
               from dba_free_space free
              where free.tablespace_name =  i.tablespace_name
                and bytes               >=  i.next_extent     ))
UNION
SELECT seg.owner, seg.segment_name,
       seg.segment_type, seg.tablespace_name,
       DECODE (seg.segment_type,
               'ROLLBACK', r.next_extent)
   FROM sys.dba_segments seg,
        sys.dba_rollback_segs r
where  (seg.segment_type = 'ROLLBACK'
  AND    seg.segment_name = r.segment_name
  AND    seg.owner        = r.owner
  AND    NOT EXISTS
            (select tablespace_name
               from dba_free_space free
              where free.tablespace_name =  r.tablespace_name
                and bytes               >=  r.next_extent     )) 
/
ttitle 'Segments that Are Sitting on the Maximum Extents Allowable '

select  e.owner, e.segment_name, e.segment_type, count(*), avg(max_extents)
 from  dba_extents e , dba_segments s
where  e.segment_name = s.segment_name
  and  e.owner        = s.owner 
group by  e.owner, e.segment_name, e.segment_type
having count(*) = avg(max_extents)                                       
/

spool off