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 	c_rbs_control.sql
REM
REM <SQLDIR_GRP>RBS MAINT</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(www.dbatoolz.com)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Examples of controlling RBS
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	c_rbs_control.sql
REM 
REM Example:
REM	c_rbs_control.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

set lines 132
set trims on

select 'alter rollback segment '||segment_name||' offline;' 
from dba_rollback_segs 
where status = 'ONLINE';

select 'alter rollback segment '||segment_name||' online;' 
from dba_rollback_segs 
where status = 'OFFLINE';

select 'drop rollback segment '||segment_name||' ;' 
from dba_rollback_segs 
where status = 'OFFLINE';

select 'alter rollback segment '||SEGMENT_NAME||' storage(minextents 20 optimal 20m);'
from dba_rollback_segs;

select 'alter rollback segment '||SEGMENT_NAME||' shrink;'
from dba_rollback_segs;

select 'alter rollback segment '||SEGMENT_NAME||' storage(maxextents 1024);'
from dba_rollback_segs;

col kbsize noprint new_value _kbsize
col optimal noprint new_value _optimal
select &&mbsize*1024||'K' kbsize
,      &&mbsize*&&minextents*1024||'K' optimal
from dual;

set lines 300
set trims on
col l format a300

select 'drop rollback segment '||SEGMENT_NAME||';'  l
,      'create rollback segment '||SEGMENT_NAME||' storage(initial &&_kbsize next &&_kbsize minextents &&minextents optimal &&_optimal maxextents unlimited)
tablespace '||tablespace_name||';'
,      'alter rollback segment '||SEGMENT_NAME||' online;'  l
from dba_rollback_segs
where status='OFFLINE';

undefine mbsize
undefine minextents