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_outbound.sql
REM
REM <SQLDIR_GRP>SEGMENT MOST</SQLDIR_GRP>
REM
REM SQLDIR Group Descriptions:
REM	APPS		-	APPS General
REM	APPS_INST	-	APPS Installation
REM	APPS_CONC_PROG	-	APPS Concurrent Programs
REM	APPS_CONC_MAN	-	APPS Concurrent Managers
REM	APPS_ADMIN	-	APPS Administration
REM	DBF		- 	Data Files
REM	TABSP		- 	Tablespace
REM	UTIL		- 	Utility
REM	INDX		- 	Index
REM	LOG		- 	Redo Log
REM	RBS		- 	Rollback
REM	MAINT		- 	Maintenance
REM	REVERSE		- 	Reverse Engineering
REM	SGA		- 	SGA Maintenance
REM	TAB		- 	Table
REM	USER		- 	User Management
REM	STATS		- 	Statistics
REM	STORAGE		- 	Storage Management
REM	INIT		- 	Database Init Parameters
REM	LATCH		- 	Latches
REM	LOCK		- 	Locks
REM	SEGMENT		- 	Segment Management
REM	BACKUP		- 	Backup Management
REM	PQ		- 	Parallel Query
REM	TRACE		- 	SQL Tracing Tuning
REM	PART		- 	Partitioning
REM	MOST		- 	Favorite Scripts
REM 
REM Author:
REM	Vitaliy Mogilevskiy www.dbatoolz.com
REM
REM
REM 
REM Purpose:
REM     <SQLDIR_TXT>
REM	Reports segments that fit the following criteria:
REM	-------------------------------------------------
REM	   a)	(160K =< SEG < 5M) NEXT < 160K
REM	   b)	(5M =< SEG < 160M) NEXT < 5M
REM	   c)	(160M =< SEG ) NEXT < 160M
REM	The report should be analyzed and segments can be altered
REM	by using the following script "s_db_segs_alter.sql"
REM     </SQLDIR_TXT>
REM
REM Usage:
REM     s_db_segs_outbound.sql
REM 
REM Example:
REM     s_db_segs_outbound.sql
REM
REM
REM History:
REM	03-26-2002	Vitaliy Mogilevskiy	Created
REM
REM
@x_db_name.sql

set feed on
set head on
set pages 100

set lines 132
col owner format a7
col segment_type format a15
col segment_name format a40
col kbytes format 999,999,999,999
col nkbytes format 999,999,999

break on d 

spool s_db_segs_outbound.&db_name..&tstamp..log

select '(160K =< SEG < 5M) NEXT < 160K' d
,      owner 
,      segment_type
,      segment_name
,      bytes/1024 kbytes
,      NEXT_EXTENT/1024 nkbytes
from dba_segments
where bytes >= 160*1024
and   bytes < 5*1024*1024
and   NEXT_EXTENT < 160*1024
and owner not in ('SYS','SYSTEM')
order by 2,3,4
/


select '(5M =< SEG < 160M) NEXT < 5M' d
,      owner 
,      segment_type
,      segment_name
,      bytes/1024 kbytes
,      NEXT_EXTENT/1024 nkbytes
from dba_segments
where bytes >= 5*1024*1024
and   bytes < 160*1024*1024
and   NEXT_EXTENT < 5*1024*1024
and owner not in ('SYS','SYSTEM')
order by 2,3,4
/


select '(160M =< SEG ) NEXT < 160M' d
,      owner 
,      segment_type
,      segment_name
,      bytes/1024 kbytes
,      NEXT_EXTENT/1024 nkbytes
from dba_segments
where bytes >= 160*1024*1024
and   NEXT_EXTENT < 160*1024*1024
and owner not in ('SYS','SYSTEM')
order by 2,3,4
/

spool off