ROLLBACK SEGMENTS in Oracle

In order to support the rollback facility in oracle database, oracle takes the help of rollback segments. Rollback segments basically holds the before image or undo data or uncommitted data of a particular transaction, once the transaction is over the blocks in that rollback segment can help any other transaction.

Rollback segment is just like any other table segments and index segments, which consist of extents, also demand space and they get created in a tablespace. In order to perform any DML operation against a table which is in a non system tablespace ('emp' in 'user' tablespace), oracle requires a rollback segment from a non system tablespace.

When a transaction is going on a segment which is in non system tablespace, then Oracle needs a rollback segment which is also in non system tablespace. This is the reason we create a separate tablespace just for the rollback segment.

Why rollback segments?

o    Undo the changes when a transaction is rolled back.

o    Ensure read consistency (other transactions do not see uncommitted changes made to the database).

o    Recover the database to a consistent state in case of failures.

There are two types of rollback segments
a) Private rollback segments (for single instance database).
b) Public rollback segments (for RAC or Oracle Parallel Server).

At the time of database creation oracle by default creates a rollback segment by name SYSTEM in system tablespace and it's ONLINE. This rollback segment can't be brought OFFLINE since Oracle needs it as long as DB is up & running. This can't be dropped also.

Only DBA can create the rollback segments (SYS is the owner) and cannot accessible to ordinary users.

SQL> CREATE [PUBLIC] ROLLBACK SEGMENT rbs-name

[TABLESPACE tbs-name]

STORAGE (INITIAL 20K NEXT 40K MINEXTENTS 2 MAXEXTENTS 50);

·         Work from home data entry

·         Quick weight loss tips

·         Oracle stock quote

·         Jobs

·         Balance

·         States

A rollback segment also has its own storage parameters, and the rules in creating RBS are:
1. We can't define PCTINCREASE for RBS (not even 0).
2. We have to have at least 2 as MINEXTENTS. 

Apart from regular storage parameters rollback segments can also be defined with OPTIMAL. We better create these rollback segments in a separate tablespace where no tables or indexes exist. We should prefer to create different rollback segments in different tablespaces.

Though we have created rollback segments, we have to bring them ONLINE, either by using init.ora or by using a SQL statement.

In order to utilize/enable rollback segments by having a parameter in init.ora, ROLLBACK_SEGMENTS = R1,R2,R3

There is another way to bring any rollback segment ONLINE, by DBA in Oracle is:
SQL> ALTER ROLLBACK SEGMENT rbs-name ONLINE;

Similarly we can also make it offline.
SQL> ALTER ROLLBACK SEGMENT rbs-name OFFLINE;

The number of rollback segments that are needed in the database are decided by the concurrent DML activity users (number of transactions). Maximum number of rollback segments can be defined in init.ora by MAX_ROLLBACK_SEGMENTS parameter (until 9i).

To execute CREATE ROLLBACK SEGMENT and ALTER ROLLBACK SEGMENT commands, UNDO_MANAGEMENT must not be set or set to MANUAL.

The assignment of the rollback segment to a transaction will be done using load balancing method (with respect to the number of transactions but not the size of transactions). A user can request oracle for a particular rollback segment for his transaction.

SQL> SET TRANSACTION USE ROLLBACK SEGMENT;
SQL> SET TRANSACTION USE ROLLBACK SEGMENT rbs-name;

To assign a rollback segment at session level

SQL> ALTER SESSION USE ROLLBACK SEGMENT rbs-name;

In a production database environment, we have to design different types of rollback segments to help different types of transactions. Usually in the day hours we have smaller transactions (data entry operations) by the end-users, and in the night we perform processing (batch jobs), example clear sql procedure updating tables and committing at the end of the transaction.

Points to ponder:

1. Oracle strongly recommends to have smaller rollback segments.

2. Its good to have not more than 4 transactions per rollback segment.

3. One transaction can only take place in one rollback segment. If there is any space problem the transaction has to fail, but cannot switch over to another rollback segment and Oracle rollbacks the transaction.

4. One rollback segment can have multiple transactions. We can limit the maximum transactions a rollback segment can support. Should limit to 10 by having an init.ora parameter TRANSACTIONS_PER_ROLLBACK_SEGMENT=10.

5. If we are having problems like "Read Inconsistencies" or "Snapshot Too Old" problems, we can do these things:

·         Increase the size of the rollback segment (so that "wrapping" issue may not occur so frequently).

·         Decrease the "Commit" Frequency, so that "blocks" can’t be overwritten as they are still belonging to "Open" DML.

6. Constantly DBA should observe the HWM (High Water Mark) line for rollback segment.

7. If you bounce the database, the rollback segment will be offline, unless you added the rollback segment name to parameter file.

ROLLBACK_SEGMENTS = r1, r2, r3, r4

8. DBA should define the optimal value for rollback segment. Otherwise if the rollback segment becomes big, it'll stay at that size which is unwanted (as Oracle recommends smaller RBS). So it's nice to comeback to some reasonable size after growing while helping a transaction. Though we define this, rollback segment by default it'll not comeback to this size right after the transaction is finished, rather it'll wait until another transaction wants to use it. Then it becomes smaller and again starts growing if necessary.

The biggest issue for a DBA is maintaining the rollback segments especially in a high-activity environment.

The reasons for a transaction to fail in Oracle, are:
1. RBS is too small to carry entire transaction. Nothing but the tablespace limitation.

2. RBS is already reached MAXEXTENTS, i.e. although tablespace has some free space to offer, rollback segment can't grow anymore because it has already grabbed it's MAXEXTENTS.

Or you have defined the NEXT EXTENT size wrongly, thus it has reached its MAXEXTENTS so quickly.

3. Our transaction, say it grabbed one rollback segment and some other transaction also grabbed the same rollback segment. In this case, our transaction couldn't find sufficient space to have all the before image blocks.

Operations on rollback segments

Shrinking rollback segment:

The rollback segment cannot shrink to less than two extents.

SQL> ALTER ROLLBACK SEGMENT rbs-name SHRINK [TO int {K|M}];

Managing storage options of rollback segment:

You cannot change the values of the INITIAL and MINEXTENTS.

SQL> ALTER ROLLBACK SEGMENT rbs-name STORAGE storage-options;

Dropping rollback segment:

you can drop only non-system and offline rollback segments.

SQL> DROP ROLLBACK SEGMENT rbs-name;

ORA-1555 error (Snapshot too old error)

$ oerr ora 1555

01555, 00000, "snapshot too old: rollback segment number seg-number with name rbs-name too small"

// *Cause: rollback records needed by a reader for consistent read are overwritten by other writers. i.e. One user continuously updating on one table where as the another user trying to retrieve continuously on that same table.

// *Action: If in automatic undo management mode, increase undo_retention setting and undo tablespace size. Otherwise, use larger/more rollback segments and avoid long running queries.

Related Views
DBA_SEGMENTS --> Here you can see all the rollback segments regardless they are offline or online (without status online/offline).
SQL> select SEGMENT_NAME, TABLESPACE_NAME from DBA_SEGMENTS where SEGMENT_TYPE='ROLLBACK';

DBA_ROLLBACK_SEGS -->ROLLBACK SEGMENTS in Oracle

In order to support the rollback facility in oracle database, oracle takes the help of rollback segments. Rollback segments basically holds the before image or undo data or uncommitted data of a particular transaction, once the transaction is over the blocks in that rollback segment can help any other transaction.

Rollback segment is just like any other table segments and index segments, which consist of extents, also demand space and they get created in a tablespace. In order to perform any DML operation against a table which is in a non system tablespace ('emp' in 'user' tablespace), oracle requires a rollback segment from a non system tablespace.

When a transaction is going on a segment which is in non system tablespace, then Oracle needs a rollback segment which is also in non system tablespace. This is the reason we create a separate tablespace just for the rollback segment.

Why rollback segments?

o    Undo the changes when a transaction is rolled back.

o    Ensure read consistency (other transactions do not see uncommitted changes made to the database).

o    Recover the database to a consistent state in case of failures.

There are two types of rollback segments
a) Private rollback segments (for single instance database).
b) Public rollback segments (for RAC or Oracle Parallel Server).

At the time of database creation oracle by default creates a rollback segment by name SYSTEM in system tablespace and it's ONLINE. This rollback segment can't be brought OFFLINE since Oracle needs it as long as DB is up & running. This can't be dropped also.

Only DBA can create the rollback segments (SYS is the owner) and cannot accessible to ordinary users.

SQL> CREATE [PUBLIC] ROLLBACK SEGMENT rbs-name

[TABLESPACE tbs-name]

STORAGE (INITIAL 20K NEXT 40K MINEXTENTS 2 MAXEXTENTS 50);

·         Work from home data entry

·         Quick weight loss tips

·         Oracle stock quote

·         Jobs

·         Balance

·         States

A rollback segment also has its own storage parameters, and the rules in creating RBS are:
1. We can't define PCTINCREASE for RBS (not even 0).
2. We have to have at least 2 as MINEXTENTS. 

Apart from regular storage parameters rollback segments can also be defined with OPTIMAL. We better create these rollback segments in a separate tablespace where no tables or indexes exist. We should prefer to create different rollback segments in different tablespaces.

Though we have created rollback segments, we have to bring them ONLINE, either by using init.ora or by using a SQL statement.

In order to utilize/enable rollback segments by having a parameter in init.ora, ROLLBACK_SEGMENTS = R1,R2,R3

There is another way to bring any rollback segment ONLINE, by DBA in Oracle is:
SQL> ALTER ROLLBACK SEGMENT rbs-name ONLINE;

Similarly we can also make it offline.
SQL> ALTER ROLLBACK SEGMENT rbs-name OFFLINE;

The number of rollback segments that are needed in the database are decided by the concurrent DML activity users (number of transactions). Maximum number of rollback segments can be defined in init.ora by MAX_ROLLBACK_SEGMENTS parameter (until 9i).

To execute CREATE ROLLBACK SEGMENT and ALTER ROLLBACK SEGMENT commands, UNDO_MANAGEMENT must not be set or set to MANUAL.

The assignment of the rollback segment to a transaction will be done using load balancing method (with respect to the number of transactions but not the size of transactions). A user can request oracle for a particular rollback segment for his transaction.

SQL> SET TRANSACTION USE ROLLBACK SEGMENT;
SQL> SET TRANSACTION USE ROLLBACK SEGMENT rbs-name;

To assign a rollback segment at session level

SQL> ALTER SESSION USE ROLLBACK SEGMENT rbs-name;

In a production database environment, we have to design different types of rollback segments to help different types of transactions. Usually in the day hours we have smaller transactions (data entry operations) by the end-users, and in the night we perform processing (batch jobs), example clear sql procedure updating tables and committing at the end of the transaction.

Points to ponder:

1. Oracle strongly recommends to have smaller rollback segments.

2. Its good to have not more than 4 transactions per rollback segment.

3. One transaction can only take place in one rollback segment. If there is any space problem the transaction has to fail, but cannot switch over to another rollback segment and Oracle rollbacks the transaction.

4. One rollback segment can have multiple transactions. We can limit the maximum transactions a rollback segment can support. Should limit to 10 by having an init.ora parameter TRANSACTIONS_PER_ROLLBACK_SEGMENT=10.

5. If we are having problems like "Read Inconsistencies" or "Snapshot Too Old" problems, we can do these things:

·         Increase the size of the rollback segment (so that "wrapping" issue may not occur so frequently).

·         Decrease the "Commit" Frequency, so that "blocks" can’t be overwritten as they are still belonging to "Open" DML.

6. Constantly DBA should observe the HWM (High Water Mark) line for rollback segment.

7. If you bounce the database, the rollback segment will be offline, unless you added the rollback segment name to parameter file.

ROLLBACK_SEGMENTS = r1, r2, r3, r4

8. DBA should define the optimal value for rollback segment. Otherwise if the rollback segment becomes big, it'll stay at that size which is unwanted (as Oracle recommends smaller RBS). So it's nice to comeback to some reasonable size after growing while helping a transaction. Though we define this, rollback segment by default it'll not comeback to this size right after the transaction is finished, rather it'll wait until another transaction wants to use it. Then it becomes smaller and again starts growing if necessary.

The biggest issue for a DBA is maintaining the rollback segments especially in a high-activity environment.

The reasons for a transaction to fail in Oracle, are:
1. RBS is too small to carry entire transaction. Nothing but the tablespace limitation.

2. RBS is already reached MAXEXTENTS, i.e. although tablespace has some free space to offer, rollback segment can't grow anymore because it has already grabbed it's MAXEXTENTS.

Or you have defined the NEXT EXTENT size wrongly, thus it has reached its MAXEXTENTS so quickly.

3. Our transaction, say it grabbed one rollback segment and some other transaction also grabbed the same rollback segment. In this case, our transaction couldn't find sufficient space to have all the before image blocks.

Operations on rollback segments

Shrinking rollback segment:

The rollback segment cannot shrink to less than two extents.

SQL> ALTER ROLLBACK SEGMENT rbs-name SHRINK [TO int {K|M}];

Managing storage options of rollback segment:

You cannot change the values of the INITIAL and MINEXTENTS.

SQL> ALTER ROLLBACK SEGMENT rbs-name STORAGE storage-options;

Dropping rollback segment:

you can drop only non-system and offline rollback segments.

SQL> DROP ROLLBACK SEGMENT rbs-name;

ORA-1555 error (Snapshot too old error)

$ oerr ora 1555

01555, 00000, "snapshot too old: rollback segment number seg-number with name rbs-name too small"

// *Cause: rollback records needed by a reader for consistent read are overwritten by other writers. i.e. One user continuously updating on one table where as the another user trying to retrieve continuously on that same table.

// *Action: If in automatic undo management mode, increase undo_retention setting and undo tablespace size. Otherwise, use larger/more rollback segments and avoid long running queries.

Related Views
DBA_SEGMENTS --> Here you can see all the rollback segments regardless they are offline or online (without status online/offline).
SQL> select SEGMENT_NAME, TABLESPACE_NAME from DBA_SEGMENTS where SEGMENT_TYPE='ROLLBACK';

DBA_ROLLBACK_SEGS --> You can see the status of a RBS.
Possible statuses are: ONLINE, OFFLINE, Pending Offline.

SQL> select SEGMENT_NAME, TABLESPACE_NAME, STATUS from DBA_ROLLBACK_SEGS;

V$ROLLNAME --> Here you USN and the RBS Name, which are ONLINE.

V$ROLLSTAT --> You can see USN, which are ONLINE, and it's complete details like

1. Size of the rollback segment.
2. Carrying any transactions or not.
3. What is the High-Water-Mark size.
4. Optimal size of the RBS.
5. Wrap information and much more info about each RBS.

From Oracle 10g, you should use UNDO Segments, instead of rollback segments.

  You can see the status of a RBS.
Possible statuses are: ONLINE, OFFLINE, Pending Offline.

SQL> select SEGMENT_NAME, TABLESPACE_NAME, STATUS from DBA_ROLLBACK_SEGS;

V$ROLLNAME --> Here you USN and the RBS Name, which are ONLINE.

V$ROLLSTAT --> You can see USN, which are ONLINE, and it's complete details like

1. Size of the rollback segment.
2. Carrying any transactions or not.
3. What is the High-Water-Mark size.
4. Optimal size of the RBS.
5. Wrap information and much more info about each RBS.

From Oracle 10g, you should use UNDO Segments, instead of rollback segments.