REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM <SQLDIR_GRP>SEGMENT</SQLDIR_GRP>
REM ********************************************************************
REM * Filename		: a_maxe.sql - Version 1.1
REM * Author		: Cary V. Millsap (V6) 
REM *			  Craig A. Shallahamer (Oracle7)
REM * Original		: 07-NOV-93
REM * Last Update	: 1/16/96 - 08:27:39
REM <SQLDIR_TXT>
REM * Description	: report segment near their extlimit boundries
REM * Usage		: start a_maxe.sql <near x of hitting maxe>
REM </SQLDIR_TXT>
REM ********************************************************************

col blockval new_value extlimit noprint

select
  decode(value,
     512,  25,	/*  512-byte block ==> effective maxextents= 25 */
    1024,  57,	/* 1024-byte block ==> effective maxextents= 57 */
    2048, 121,	/* 2048-byte block ==> effective maxextents=121 */
    4096, 249,	/* 4096-byte block ==> effective maxextents=249 */
    8192, 505,	/* 8192-byte block ==> effective maxextents=505 */
    NULL
  ) blockval
from
  aps_parameter
where
  lower(name) = 'db_block_size'
/


def thresh	= &&1

def aps_prog	= 'maxe.sql &thresh'
def aps_title	= 'Segments within &thresh Extents of Effective Max'
start apstitle

col segname format   a42 justify c trunc heading 'Segment'
col segtype format   a10 justify c trunc heading 'Type'
col excount format 9,990 justify c trunc heading 'Current|Extents'
col maxexts format 9,990 justify c trunc heading 'Max|Extents'
col extdiff format 9,990 justify c trunc heading 'Extents|Remaining'

select
  owner||'.'||segment_name              segname,
  segment_type                          segtype,
  extents                               excount,
  least(max_extents,&extlimit)          maxexts,
  least(max_extents,&extlimit)-extents  extdiff
from
  dba_segments
where
  (least(max_extents,&extlimit)-extents <= &thresh)
order by
  extents/(least(max_extents,&extlimit)+0.00001)  desc,
  least(max_extents,&extlimit)-extents    desc
/

undef thresh
undef extlimit
start apsclear