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_alter.sql
REM
REM <SQLDIR_GRP>SEGMENT MOST TABSP</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Alters segment's storage parameters (NEXT <X> and PCTINCREASE 0)
REM	where <X> is derived based on the current size from dba_segments:
REM	
REM	There is a theory by which you only need 3 types
REM	of tablespaces:
REM	  160k (SMALL)
REM	  5MBm (MEDIUM)
REM	  160MB (LARGE)
REM	If you follow this theory you can use this script
REM	to "size" your segment's future extents for the "right" tablespace:
REM	  1. 160K ts (segs < 5MB)
REM	  2. 5MB ts (segs < 160MB)
REM	  3. 160MB ts (segs > 160MB)
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_db_segs_alter.sql
REM 
REM Example:
REM	s_db_segs_alter.sql
REM
REM
REM History:
REM	03-08-2002	VMOGILEV	Created
REM
REM

accept 1 prompt "Enter owner for the segments: "
accept 2 prompt "Enter segment type (like)[ TABLE% or INDEX% ]: "
prompt ... Enter number of extents to limit the list (optional),
prompt ... for example to alter segments that have > 50 extents
prompt ... enter 50 at the prompt below:
accept 3 prompt "Extents limit (optional): "


set pages 0
set lines 332
set trimspool on
set verify off
set feed off
set term off
set echo off

select 'alter '||DECODE(INSTR(segment_type,'PARTITION'),
                    0,segment_type
                     ,REPLACE(segment_type,'PARTITION'))||
       ' '||owner||'.'||segment_name||DECODE(INSTR(segment_type,'PARTITION'),
                                         0,''
                                          ,' MODIFY PARTITION '||partition_name)||
       ' storage (next 160K pctincrease 0);'
from dba_segments 
where owner='&&1' 
and   segment_type like '&&2'
and bytes < 5*1024*1024
and next_extent <> 160*1024
and extents > NVL('&&3',0)
union all
select 'alter '||DECODE(INSTR(segment_type,'PARTITION'),
                    0,segment_type
                     ,REPLACE(segment_type,'PARTITION'))||
       ' '||owner||'.'||segment_name||DECODE(INSTR(segment_type,'PARTITION'),
                                         0,''
                                          ,' MODIFY PARTITION '||partition_name)||
       ' storage (next 5M pctincrease 0);'
from dba_segments 
where owner='&&1' 
and   segment_type like '&&2'
and (bytes < 160*1024*1024 and bytes >= 5*1024*1024)
and next_extent <> 5*1024*1024
and extents > NVL('&&3',0)
union all
select 'alter '||DECODE(INSTR(segment_type,'PARTITION'),
                    0,segment_type
                     ,REPLACE(segment_type,'PARTITION'))||
       ' '||owner||'.'||segment_name||DECODE(INSTR(segment_type,'PARTITION'),
                                         0,''
                                          ,' MODIFY PARTITION '||partition_name)||
       ' storage (next 160M pctincrease 0);'
from dba_segments 
where owner='&&1' 
and   segment_type like '&&2'
and bytes >= 160*1024*1024
and next_extent <> 160*1024*1024
and extents > NVL('&&3',0)


spool alter.&&1..&&2..run
/
spool off

set term on
set feed on

ed alter.&&1..&&2..run

accept dummy prompt "Enter to continue or CTL-C to stop the script from running ..."
set echo on
spool alter.&&1..&&2..log
@alter.&&1..&&2..run
spool off
set echo off