rem *  **************************************************** *
rem *  name: c:\oracle\sql\extent_1.sql ........            *
rem *                  objects that exceed my extent count  *
rem *  owner: R.Kupcunas                                    *
rem *  date:    30-APR-95                                   *
rem *  **************************************************** *

spool extent_1.sql

set wrap on

set feedback off
set pagesize 60
set heading on
set echo off
set verify off
set pause off
set newpage 0
set term on
set recsep off

/* ****************************** */
/* "Extents that exceed a value"  */
/* ****************************** */

ttitle center skip 2 'SEGMENTS THAT EXCEED A GIVEN EXTENT VALUE' skip 2

/* ************** */
/* Analyze Tables */
/* ************** */

select substr(segment_name, 1, 30)    "SEGMENT NAME",
       segment_type                   "TYPE",
       max_extents,
       sum(extents)
  from dba_segments
  where segment_type = 'TABLE'
  group by segment_name,
       segment_type,
       max_extents
  having sum(extents) > &&EXTENT_THRESHOLD_VALUE;

/* *************** */
/* Analyze Indexes */
/* *************** */

select substr(segment_name, 1, 30)    "SEGMENT NAME",
       segment_type                   "TYPE",
       max_extents,
       sum(extents)
  from dba_segments
  where segment_type = 'INDEX'
  group by segment_name,
       segment_type,
       max_extents
  having sum(extents) > &&EXTENT_THRESHOLD_VALUE;

/* ************************* */
/* Analyze Rollback Segments */
/* ************************* */

select substr(segment_name, 1, 30)    "SEGMENT NAME",
       segment_type                   "TYPE",
       max_extents,
       sum(extents)
  from dba_segments
  where segment_type = 'ROLLBACK'
  group by segment_name,
       segment_type,
       max_extents
  having sum(extents) > &&EXTENT_THRESHOLD_VALUE;

/* **************** */
/* Analyze Clusters */
/* **************** */

select substr(segment_name, 1, 30)    "SEGMENT NAME",
       segment_type                   "TYPE",
       max_extents,
       sum(extents)
  from dba_segments
  where segment_type = 'CLUSTER'
  group by segment_name,
       segment_type,
       max_extents
  having sum(extents) > &&EXTENT_THRESHOLD_VALUE;

/* ********************************** */
/* Analyze Anything besides the above */
/* ********************************** */

select substr(segment_name, 1, 30)    "SEGMENT NAME",
       segment_type                   "TYPE",
       max_extents,
       sum(extents)
  from dba_segments
  where segment_type != 'TABLE' and
        segment_type != 'INDEX' and
        segment_type != 'CLUSTER' and
        segment_type != 'ROLLBACK'
  group by segment_name,
       segment_type,
       max_extents
  having sum(extents) > &&EXTENT_THRESHOLD_VALUE;

spool off;
commit;
/