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.
- Inadequate space in the rollback segment (RBS) tablespace for rollback segment expansion.
- Improper storage specifications for the rollback segment being used resulting in that segment exceeding its MAXEXTENTS value.
- 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
- rollback_name - must be unique.
- tablespace_name - if not specified then the rollback segment is created in the default tablespace for the user creating the rollback segment.
- storage_clause - use the following guidlines.
- INITIAL = NEXT INITIAL - this sets the size of the initial rollback segment. NEXT sets the size for the next extent in the rollback segment and subsequent extents assuming PCTINCREASE is set to zero. Note that NEXT is modifiable after rollback segment creation.
- MINEXTENTS - default is 20. Sets the minimum number of extents that are initially allocated when a rollback segment is created.
- MAXEXENTS - this is a calculated maximum based on the size of the rollback segment tablespace, the size of the rollback segment extents and the number of rollback segments. If MAXEXTENTS is set to UNLIMITED then the MAXEXTENTS parameter could allow the rollback segment to use up all available rollback tablespace free area (disk space allocated to rollback segments). MAXEXTENTS can be modified after rollback segment creation.
- OPTIMAL - this reflects the size that the system will restore the rollback segment to by deallocating extents after the rollback segment has been increased by a large transaction. OPTIMAL must be set to allow average-sized transactions to complete without causing wrapping or causing shrinkage. OPTIMAL can be modifed after rollback segment creation.
- PCTINCREASE - this should always be set to zero. Oracle versions 7.3.3 and above do not allow the setting of this parameter.
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