Oracle Rollback Segments

Rollback segments store system undo data allowing non-commited transactions to be rolled back. In other databases rollback segments are before-image logs or journals. Some databases also have check-pointing. Check-pointing allows for rollback to specific points in multiple statement transactions thus avoiding rollback on a whole sequence of transactions. Rollback segments store the before images of changed data. Large transaction failures are usually caused by one of the following problems.

  1. Inadequate space in the rollback segment (RBS) tablespace for rollback segment expansion.
  2. Improper storage specifications for the rollback segment being used resulting in that segment exceeding its MAXEXTENTS value.
  3. Improper scheduling allowing other transactions to cause Snapshot Too Old errors to occur in the transaction. This can also be caused by improper setting of INITRANS.

The SYSTEM rollback segment is created on database creation and is used strictly by the SYSTEM tablespace. There must be atleast one additional rollback segment created. It is important to determine how many users should be assigned to rollback segments based on the number of concurrent users. This is decided by the setting of MINEXTENTS. For instance, if there are 100 concurrent users and you require 20 users per rollback segment then MINEXTENTS should be set to 20 for each of 5 rollback segments. With respect to private rollback segments, those owned by specific users, the initialisation parameters TRANSACTIONS divided by TRANSACTIONS_PER_ROLLBACK_SEGMENT should be used to decide the number of rollback segments to be created.

CREATE Rollback Segment

A rollback segment is created offline. Rollback segments can be brought online using the ALTER ROLLBACK SEGMENT ONLINE command. The ROLLBACK_SEGMENTS control file parameter can be altered, and the database can be restarted. It is best to utilise this parameter. Sometimes not all the rollback segments will be brought online on database startup if the ROLLBACK_SEGMENTS parameter does not include all the rollback segments required to be used.

ALTER Rollback Segment

Only items dealing with storage characteristics or rollback segment status and size can be altered. The NEXT, OPTIMAL and MAXEXTENTS parameters can be altered. Rollback segments can be placed online or offline and can be shrunk using the SHRINK clause to a given size or that size provided by OPTIMAL.

DROP Rollback Segment