rem *********************************************************************
rem *                                                                   *
rem * Name      : toobig.sql                                            *
rem * Synopsis  : Shows segments that need attention due to their size  *
rem * Source    : http://www.oracle-dba.fr.pl                           *
rem *                                                                   *
rem *********************************************************************

PROMPT
PROMPT ************************************************
PROMPT *** SEGMENTS WITHIN 10 EXTENTS OF MAXEXTENTS ***
PROMPT ************************************************

column segment         format a40                        wrap
column segment_type    format a10     heading "SEG TYPE" wrap
column max_extents     format 9999999 heading "MAXEXTS"
column next_extent                    heading "NEXTEXT"
column initial_extent                 heading "INITEXT"
column tablespace_name format a15 wrap

set linesize  1000
set pagesize  32000
set trimspool on

select  extents
,       max_extents
,       owner||'.'||segment_name segment
,       segment_type
,       bytes
,       next_extent
,       initial_extent
,       tablespace_name
from    sys.dba_segments
where   max_extents - extents < 10
/

PROMPT
PROMPT ***********************************
PROMPT *** SEGMENTS THAT CANNOT EXTEND ***
PROMPT ***********************************

select  a.owner||'.'||a.segment_name segment
,       a.segment_type
,       b.tablespace_name
,       decode(ext.extents,1,b.next_extent, a.bytes*(1+b.pct_increase/100)) nextext
,       freesp.largest
from    dba_extents a
,       dba_segments b
,       (select   owner
         ,        segment_name
         ,        max(extent_id) extent_id
         ,        count(*) extents
         from     dba_extents
         group by owner
         ,        segment_name) ext,
        (select   tablespace_name
         ,        max(bytes) largest
         from     dba_free_space
         group by tablespace_name) freesp
where   a.owner           = b.owner
and     a.segment_name    = b.segment_name
and     a.owner           = ext.owner
and     a.segment_name    = ext.segment_name
and     a.extent_id       = ext.extent_id
and     b.tablespace_name = freesp.tablespace_name
and     decode(ext.extents,1,b.next_extent, a.bytes*(1+b.pct_increase/100)) > freesp.largest
/

column segment         clear
column segment_type    clear
column max_extents     clear
column next_extent     clear
column initial_extent  clear
column tablespace_name clear