ROLLBACK_SEGMENTS and UNDO_MANAGEMENT

ROLLBACK SEGMENTS are used to store undo data for uncommited transactions.

In the event of instance failure, Oracle will use the data inside the ROLLBACK SEGMENTS to undo uncommited data inside the database. To enable ROLLBACK_SEGMENTS,

SQL> alter system set undo_management=manual scope=spfile;

SQL> create tablespace rollback_ts datafile 
      '$ORACLE_BASE/oradata//rollback_ts01.dbf' size 50M logging extent 
      management local segment space management manual;   

SQL> create rollback segment rollback_seg tablespace rollback_ts;

SQL> alter system set rollback_segments='rollback_seg' scope=spfile;

SQL> select segment_name, segment_type 

       from dba_segments

      where segment_type='ROLLBACK';

SQL> startup nomount force;

Upon instance restart, Oracle will use the segment rollback_seg specified in the ROLLBACK_SEGMENTS parameter to contain all undo transaction informaton. 

UNDO tablespaces carry out the same task as ROLLBACK SEGMENTS. They to keep the original data modified in uncommited transactions.

UNDO tablespaces are easier to administrate and supposedly more efficient.

To utilise UNDO tablespaces, first the database needs to be put into automatic undo management by running,

SQL> alter system set undo_management=AUTO scope=pfile

An undo tablespace needs to be created to handle the undo data

SQL> create undo tablespace 'UNDOTBS2' datafile  
      '/u01/app/oracle/oradata/orcl/undotbs1.dbf' size 100M retention guarantee

SQL> alter system set undo_tablespace = 'UNDOTBS1';

The retention guarantee option ensures that Oracle does not overwrite data in the undo tablespace which has not passed the expiration date specified in the dynamic UNDO_RETENTION system parameter - even in the event of the tablespace filling up completely.

Last changes being applied to system parameter file