What is Rollback Segment?

In this post, we will discuss the rollback segment in detail. Before the rollback segment, we need to understand transaction processing.

A transaction is nothing but a logical unit of work that made changes inside the database. When a transaction execution is completed then we have two options either to make changes to the database using the commit command or rollback to restore the previous values of the database using the rollback command.

Example of Transaction: Suppose we are performing the task of updating the first name of the employee to “John” whose id is 100. Then SQL command to do so would be

UPDATE employee 
SET firstname="John"
WHERE id=100;

Here we could use commit to reflect changes into the database or use rollback in case of any failure. That means something went wrong and we didn’t want to make changes inside our database.

What is the Rollback Segment?

In the ORACLE database, the rollback segment is a structure that stores previous data during transactions. To support the rollback facility we have a rollback segment in the ORACLE database.

Let’s consider the update statement again. To update the first name of the employee to “John” whose id is 100. Suppose the earlier name of the employee was “henry”

Query:

UPDATE employee
SET firstname="John"
WHERE id=100;

Now we are changing the name of the employee from “Henry” to “John”.

When we do that then ORACLE stores the previous value i.e, “Henry” in the rollback segment. Now we have two conditions either to do commit or rollback.

Suppose we enter the commit command then as soon as we issue the commit command ORACLE knows that whatever information has been stored into the rollback segment is no more needed because we didn’t want to store the previous value anymore. So ORACLE empties the transaction history from the rollback segment and changes are marked as finalized into the actual table.

And if we had issued a rollback command then all the previous values stored into the rollback segment would be applied back to the table.

Note: Before 10g, DBAs could create their own rollback segment. In 11g, the rollback segment could only exist in the SYSTEM tablespace 

Uses of Rollback Segment:

Below are the uses of the rollback segment:

• If during a transaction any failures occur then it makes sure that changes are not reflected back into our original database.
• It helps us in restoring our database in a consistent state in case of any failure. To ensure data consistency and isolation property.
• The rollback segment is used to recover our data in case of database failure.
• With the help of the Rollback command, we can restore our previous data which is stored inside the rollback segment to our original database.

How to CREATE/ALTER/DROP a Rollback Segment?

As we know that Oracle database uses the rollback segment to recover our consistent data in case of any database failure be it hardware failure or software failure.

Create: We can create a rollback segment by following way:

ALTER ROLLBACK SEGMENT name_of_the_segment 
[STORAGE Storage_Clause]
[ONLINE | OFFLINE]  
[SHRINK]

Drop:  Drop the rollback segment then we can do it in the following way:

DROP ROLLBACK SEGMENT name_of_the_segment

So we can say that the Rollback segment stores a history of changes. The rollback segment is used if the user uses “rollback”. Rollback segment is used to present “before” data image until the user performed the commit command.