REM ***********************************************************************
REM * @c:\oracle\sql\segsize.sql                           
REM *                                                        
REM * Owner: R.Kupcunas                                      
REM * Date:  01-MAY-95                                       
REM *                                       
REM * Script: Segment Sizing                                              *
REM * Summarize segments that contain more than a specified number of     *
REM * extents within a database.                                          *
REM * The displayed or resulting segments are then likely candidates for  *
REM * exporting, dropping, and re-creating in a single extent             *
REM * Input: Number of extents threshold above which to report            *
REM ***********************************************************************

 accept limits prompt 'Enter the minimum number of extents to report on: ';
 prompt

 col segname       format     a35  heading 'Segment' justify c trunc
 col segment_type  format     a20  heading 'Type'    justify c trunc
 col extents       format   9,990  heading 'Extents' justify c trunc

 select
  owner||'.'||segment_name segname,
  segment_type,
  extents
    from sys.dba_segments
     where
      (extents >= &limits)
      order by extents desc, 1;

undef limits;
commit;
