Title: Viewing Critical Object Extents

Author:Marco Pagan, with Geometrain in Bergamo, Italy.

This select shows all objects, tables, or indexes, the number of extents allocated, the max number of extents allocable and the
number of extents again possible before the Oracle error. The having statement restricts the output.

  select ext.owner,
         segment_name name,
         segment_type type,
         sum(bytes)/1024 sum_kbytes,
         count(bytes) num_ext,
         decode(segment_type, 'TABLE', tab.max_extents,
                              'INDEX', ind.max_extents) max_ext,
         decode(segment_type, 'TABLE', tab.max_extents,
                              'INDEX', ind.max_extents) -
                count(bytes) ext_poss ,
         ext.tablespace_name tbs
  from dba_indexes ind,
       dba_tables tab,
       dba_extents ext
  where ind.owner           (+) = ext.owner
    and ind.index_name      (+) = ext.segment_name
    and tab.owner           (+) = ext.owner
    and tab.table_name      (+) = ext.segment_name
    and ext.owner               not in ('SYS', 'SYSTEM')
  group by ext.owner,
           segment_name,
           segment_type,
           decode(segment_type, 'TABLE', tab.max_extents,
                                'INDEX', ind.max_extents),
           ext.tablespace_name
  having decode(segment_type, 'TABLE', tab.max_extents,
                              'INDEX', ind.max_extents) -
         count(bytes) < 20
      or count(bytes) > 300;