SQL Script

Generate Create Rollback Segment Script

Updated : 14-March-2002
Version : 2.0

Description

This script generates a create rollback segment script. It is especially useful of you need a duplicate database on a different machine and have a large number of rollback segments to create. This script also shows a good technique for formatting this sort of generated script by using the 'column' command.

Parameters

None.

SQL Source

set pages 0
set heading off

col col0 newline

spool genroll.lst

SELECT
'CREATE ROLLBACK SEGMENT '||rn.name||' TABLESPACE '||rs.tablespace_name col0,
'STORAGE ' col0,
'(INITIAL '||rs.initial_extent col0,
'NEXT '||rs.next_extent col0,
'MINEXTENTS '||rs.min_extents col0,
'MAXEXTENTS '||rs.max_extents col0,
'OPTIMAL '
||DECODE(r.optsize,null,rs.initial_extent+rs.next_extent,r.optsize)||');'
FROM sys.dba_rollback_segs rs, v$rollstat r, v$rollname rn
WHERE rn.name = rs.segment_name 
AND rn.usn = r.usn 
AND rn.name != 'SYSTEM'
order by rn.name
/

spool off