Rollback Segment

From Oracle FAQ
Jump to: navigation, search

A Rollback Segment is a database object containing before-images of data written to the database.

Rollback segments are used to:

  • Undo changes when a transaction is rolled back
  • Ensure other transactions do not see uncommitted changes made to the database
  • Recover the database to a consistent state in case of failures

Instead of creating Rollback Segments, users running Oracle 10g or greater should use Undo Segments instead.

[edit] Create

Use the CREATE ROLLBACK SEGMENT command:

CREATE ROLLBACK SEGMENT rs1 TABLESPACE rbsts;
ALTER ROLLBACK SEGMENT rs1 ONLINE;

When done, edit your INIT.ORA file and add the newly created rollback segment to the ROLLBACK SEGMENTS parameter.

[edit] Monitor

A list of rollback segments can be selected from DBA_ROLLBACK_SEGS:

SELECT segment_name, status FROM dba_rollback_segs;
SEGMENT_NAME         STATUS
-------------------- ----------------
SYSTEM               ONLINE
_SYSSMU2_1186132793$ ONLINE
_SYSSMU1_1186132793$ ONLINE

To monitor rollback segment activity, query the v$rollname and v$rollstat/v$undostat views:

SELECT rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)", rs.Gets "Gets",
       rs.waits "Waits", (rs.Waits/rs.Gets)*100 "% Waits",
       rs.Shrinks "# Shrinks", rs.Extends "# Extends"
FROM   sys.v_$rollName rn, sys.v_$rollStat rs
WHERE  rn.usn = rs.usn;

[edit] Also see

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #