SQL Script Generate Create Rollback Script

Updated: 14-Mar-2004
Version: 3.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 uses the DBMS_METADATA package

Parameters

None

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 


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

Related Links

Oracle 8i: (SQL Script) Generate Create Rollback Segments

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links