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 
REM File:
REM 	s_db_segs_blowout.sql
REM
REM <SQLDIR_GRP>TABSP STORAGE TAB INDX SEGMENT MAINT</SQLDIR_GRP>
REM 
REM Author:
REM	??
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	THIS Script reports segments that will "blowout"
REM	on the next extent.
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_db_segs_blowout.sql
REM 
REM Example:
REM	s_db_segs_blowout.sql
REM
REM
REM History:
REM	??-??-????	????????	Created
REM	08-01-1998	VMOGILEV	Added to DBATOOLZ library
REM
REM

-- blowout.sql -------------------------
set lines 132

/*
Rem =======================================
Rem = Segments beyond 80 % of maxextents  =
Rem =======================================
*/

col segment_name format a35 word_wrapped
col segment_type format a15 word_wrapped

select segment_name,segment_type,extents,max_extents
from dba_segments
where extents>=max_extents*0.8 and
      segment_type<>'CACHE';

/*
Rem ==============================================
Rem = Segments blowing out TS in the next extend =
Rem===============================================
*/


col segment_name format a35 word_wrapped
col segment_type format a15 word_wrapped
col tablespace_name format a25 word_wrapped
col "NEXT(MB)" format 9,999.99
col max_free format 9,999.99

select s.segment_name,
       s.segment_type,
       s.tablespace_name,
       s.next_extent/1024/1024 "NEXT(MB)",
       f.max_free/1024/1024 max_free
from dba_segments s, (select tablespace_name,max(bytes) max_free
                      from dba_free_space
                      group by tablespace_name) f
where s.tablespace_name=f.tablespace_name and
      s.next_extent>=f.max_free;

/*
Rem =============================
Rem = These TSs need coalescing =
Rem =============================
*/


select tablespace_name, percent_extents_coalesced
from dba_free_space_coalesced
where percent_extents_coalesced <> 100;

-- blowout.sql -------------------------