set linesize 80;
set pagesize 10000;
set long 50;
set echo on;

/* ************************************************ */
/*                                                  */
/* name:    bld_rollback.sql                        */
/* creator: R.Kupcunas(TSC)                         */
/* date:    2000-May-15                             */
/* purpose: script to spool a listing of all drop   */
/*          and create statements required to       */
/*          rebuild the rollback segments.          */
/*                                                  */
/* ************************************************ */

set termout off;
set echo off;
set feedback off;

col dummy     noprint format a1;
col dummy2    noprint format a1;
col table_name        noprint format a1;
col command   format a70;

/* ********************************* */
/* 1. Generate drop table commands   */
/* 2. Generate create table commands */
/* ********************************* */

/* set echo on;
set feedback on;
set termout on; */

/* ************************** */
/* Build the drop command     */
/* ************************** */
select 0 						dummy,
'DROP '||decode(owner, 'PUBLIC', 'PUBLIC ', '')||'ROLLBACK SEGMENT '||segment_name||';'
  from dba_rollback_segs
UNION
/* ************************** */
/* Build the create command   */
/*  - online RBS only         */
/* ************************** */
select 1 						dummy,
'CREATE '||decode(owner, 'PUBLIC', 'PUBLIC ', '')||'ROLLBACK SEGMENT '||segment_name|| '
       tablespace '||tablespace_name||' 
       storage (initial    '||initial_extent/(1024*1024)||'M 
                next       '||next_extent/(1024*1024)||'M 
                minextents '||MIN_EXTENTS||' 
                maxextents '||MAX_EXTENTS||'
                optimal    '||A.optsize/(1024*1024)||'M);'
from v$rollstat            A,
     dba_rollback_segs 	C
where C.segment_id = A.USN 
union
/* ************************** */
/* Build the create command   */
/*  - offline RBS only        */
/* ************************** */
select 2 						dummy,
'CREATE '||decode(owner, 'PUBLIC', 'PUBLIC ', '')||'ROLLBACK SEGMENT '||segment_name|| '
       tablespace '||tablespace_name||' 
       storage (initial    '||initial_extent/(1024*1024)||'M 
                next       '||next_extent/(1024*1024)||'M 
                minextents '||MIN_EXTENTS||' 
                maxextents '||MAX_EXTENTS||'
                optimal    ????M);'
from      dba_rollback_segs C
where C.segment_id not in (select A.USN from v$rollstat A)
  order by 1,2;

spool create_rollback_segs.sql
/
spool off;
set heading on;
set pagesize 100;
set termout on;

commit;










