doc

    Name:   5extents.sql

    Author: Mark Gurry


The following script lists all database objects that have more than 5 
extents and may be candidates for rebuilding. 

#

ttitle ' List All Segments with More than 5 Extents '
select owner, tablespace_name, segment_name|| 
       decode(segment_type,'TABLE','[T]', 'INDEX', '[I]', 
                    'ROLLBACK','[R]', '[O]') segment_name,
       sum(bytes) sizing,
       decode(count(*),1,to_char(count(*)),
                       2,to_char(count(*)),
                       3,to_char(count(*)),
                       4,to_char(count(*)),
                       5,to_char(count(*)),
                         to_char(count(*))||' < Re-build') seg_count 
  from dba_extents
 group by owner, tablespace_name, segment_name|| 
         decode(segment_type,'TABLE','[T]', 'INDEX', '[I]', 
                'ROLLBACK','[R]', '[O]')
having count(*) > 5;