Rollback Segments

The primary purpose of rollback segments is to hold old values. Means that when you update a value the updated value is stored in the table and the old value is stored in the rollback segment. So whenever that old value is needed again it is restored from rollback segment. There are three main purposes of storing old values and thus of rollback segments:

1. Transaction Rollback: Suppose you are working in a table. And you updated the name column value of a particular row from Scott to James. And then suddenly you realized that change you made was not right so you give the command of rollback. The original value i.e. Scott will be restored again. How did database come to know that the old value was Scott? The answer is through rollback segment because it’s the rollback segment that stores the old values.
2. Transaction Recovery: The second purpose is transaction recovery. Suppose you have made a change and have not committed it. But another user working on the same database gave the command of commit. LGWR will write because the commit command is given. And when LGWR writes it writes all the changes from redo buffer cache to on-line redo logs. But remember along other things redo logs also store the SCN of transaction and also whether it is committed by its user or not. Some how your DBWR also fires and writes the uncommitted change to data files. At this point in time your power supply goes off. Next time when instance will startup the SMON process will see that which transactions are stored correctly and also which are committed by their own users and which are not. If any transaction is not committed by its own user just like one above it will restore its old value; and it will take its old value from rollback segment.
3. Read Consistency: Read consistency is that something that every good database must posses. It is a sort of yard stick through which the maturity of a RDBMS is measured. In oracle this feature is implemented by making use of rollback segments. Suppose a user has given the query to fetch the first five rows from a table. The query has only fetched first three rows that another query came and updated the last two rows. Now the first query should get the old values because it was given first then the query who updated the rows. Surely it will get the old values but not from the table but from rollback segment. However any other query given after the update command will get the new updated values. In this example two features of oracle came in front of us. They are consistency and concurrency. By the way consistency must always be given the higher priority.

Rollback segments are maintained in a circular fashion. Usually more then one transaction can write in one rollback segment. Up till 8i it was not possible for multiple transactions to write in one extent. But in later versions of 8i and in above all versions it is possible. But still it is not possible for multiple transactions to write in one data block. Once one extent is full it is switched to the other. This is known as Wrap. When all the extents are full then instead of over writing the first one a new extent is created at run time. It is called Extend.

Rollback segments are automatically freed up when the data in them is no longer required. Suppose your rollback segment was full up to 100M and then automatically freed up to 4M. In this case what will happen of extra space? Is it wasted? No it is not wasted instead oracle shrinks the rollback segment up to a value stored in the optimal parameter. Normally the value of optimal parameter is 4M.

With these considerations we have a legacy problem. Consider again the example given in the Read Consistency. First command wants to fetch the first five rows of a table; it has only fetched the first three that the second command updated the last two rows. At this point in time suppose oracle has shrunk the rollback segment; where the first command will get the old values? The answer is from no where. Till 8i it was a huge problem and it is famous too. It is known as “snapshot too old” and the oracle message number for this error is ora-15500. In 9i and above versions it is automatically controlled by oracle itself.

To get information about the rollback segments you can use v$rollname, v$rollstat data dictionary views. To get information about your current active transactions you can use the view named as v$transaction.