Allocation of a Rollback Segment When a transaction begins, a rollback segment must be assigned to this transaction. A transaction can request a specific rollback segment using the following command: SET TRANSACTION USE ROLLBACK SEGMENT rollback_segment If no such request is made, the Oracle server chooses the rollback segment with the fewest transactions and assigns it to the transaction. Using Extents Transactions use extents of a rollback segment in a sequential, circular fashion, moving from one to the next after the current extent is full. A transaction writes an entry to its current location in the rollback segment and advances the current pointer by the. | B Manually Managing Undo Data (Rollback Segments) Objectives After completing this lesson, you should be able to do the following: Create rollback segments using appropriate storage settings Maintain rollback segments Plan the number and size of rollback segments Troubleshoot common rollback segment problems Creating Rollback Segments CREATE ROLLBACK SEGMENT rbs01 TABLESPACE rbs STORAGE ( INITIAL 100K NEXT 100K MINEXTENTS 20 MAXEXTENTS 100 OPTIMAL 2000K ); Creating Rollback Segments Use the following command to create a rollback segment: CREATE [PUBLIC] ROLLBACK SEGMENT rollback_segment [TABLESPACE tablespace] [STORAGE ( [INITIAL integer[K|M]] [NEXT integer[K|M]] [MINEXTENTS integer] [MAXEXTENTS {integer|UNLIMITED}] [OPTIMAL {integer[K|M]|NULL}] ) ] Creating Rollback Segments (continued) Restrictions A rollback segment can be specified as either public or private (the default) at the time of creation and cannot be changed. For a rollback segment, MINEXENTS must be at least two. . | B Manually Managing Undo Data (Rollback Segments) Objectives After completing this lesson, you should be able to do the following: Create rollback segments using appropriate storage settings Maintain rollback segments Plan the number and size of rollback segments Troubleshoot common rollback segment problems Creating Rollback Segments CREATE ROLLBACK SEGMENT rbs01 TABLESPACE rbs STORAGE ( INITIAL 100K NEXT 100K MINEXTENTS 20 MAXEXTENTS 100 OPTIMAL 2000K ); Creating Rollback Segments Use the following command to create a rollback segment: CREATE [PUBLIC] ROLLBACK SEGMENT rollback_segment [TABLESPACE tablespace] [STORAGE ( [INITIAL integer[K|M]] [NEXT integer[K|M]] [MINEXTENTS integer] [MAXEXTENTS {integer|UNLIMITED}] [OPTIMAL {integer[K|M]|NULL}] ) ] Creating Rollback Segments (continued) Restrictions A rollback segment can be specified as either public or private (the default) at the time of creation and cannot be changed. For a rollback segment, MINEXENTS must be at least two. PCTINCREASE cannot be specified for a rollback segment and is always set to 0. OPTIMAL, if specified, must be at least equal to the initial size of the rollback segment, which is the space used by the number of extents defined by MINEXTENTS. Guidelines Always use INITIAL = NEXT for rollback segments to ensure that all extents are of the same size. Set the OPTIMAL value to minimize the allocation and deallocation of rollback segment extents. Avoid setting MAXEXTENTS to UNLIMITED. This could cause unnecessary extension of a rollback segment and possibly of data files due to a program error. Always place rollback segments in a separate, exclusive tablespace to minimize contention and fragmentation. Transactions and Rollback Segments Transaction 1 Active extent Inactive extent 4 3 1 2 Transaction 2 Allocation of a Rollback Segment When a transaction begins, a rollback segment must be assigned to this transaction. A transaction can request a specific rollback segment using the following command:
