Oracle7 Server Administrator's Guide

Explicitly Assigning a Transaction to a Rollback Segment

A transaction can be explicitly assigned to a specific rollback segment using the SET TRANSACTION command with the USE ROLLBACK SEGMENT parameter. Transactions are explicitly assigned to rollback segments for the following reasons:

The anticipated amount of rollback information generated by a transaction can fit in the current extents of the assigned rollback segment.

Additional extents do not have to be dynamically allocated (and subsequently truncated) for rollback segments, which reduces overall system performance.

No special privileges are required to assign a transaction to a specific rollback segment explicitly.

To assign a transaction to a rollback segment explicitly, the rollback segment must be online for the current instance, and the SET TRANSACTION USE ROLLBACK SEGMENT statement must be the first statement of the transaction. If a specified rollback segment is not online or a SET TRANSACTION USE ROLLBACK SEGMENT statement is not the first statement in a transaction, an error is returned.

For example, if you are about to begin a transaction that contains a significant amount of work (more than most transactions), you can assign the transaction to a large rollback segment, as follows:

SET TRANSACTION USE ROLLBACK SEGMENT large_rs1;

After the transaction is committed, Oracle will automatically assign the next transaction to any available rollback segment unless the new transaction is explicitly assigned to a specific rollback segment by the user.