Oracle ROLLBACK

Introduction to Oracle ROLLBACK

Oracle Rollback statement asks the Oracle database to rollback the entire transaction which means that it will undo or omit any work or changes that may be done by the current transaction (INSERT UPDATE OR DELETE) and the database will be brought to the previous state (which means the state of the database before the current transaction was created or started) and also it can be used to manually force any corrupt or in-doubt transaction to omit its changes and restore the database to the previous state.

Syntax

We will now discuss the syntax of the ROLLBACK statement in Oracle below. The syntax is simple

ROLLBACK [WORK ] [ TO savepoint| FORCE 'string' ];

Let us now get to know the various parameters used in the ROLLBACK statement.

Parameters used in the ROLLBACK Statement

Below are some parameters:

• Work: It is an optional parameter. The statement with or without WORK clause makes no difference in the output. It was just added by Oracle to be SQL compliant.
• Savepoint: It is also optional parameter. If we use savepoint then the oracle omits all the changes done by the current session to the savepoint time provided by the clause. In case this clause is not provided then the database undoes all the changes done.
• Force ‘string’: It is also an optional parameter. It is used to rollback or omits in-doubt and pending transaction. We need to specify the transaction id in string with this clause. The transaction id can be seen in system view DBA_2PC_PENDING for corrupt or in-doubt transactions. The points to note is that the user should have DBA privileges to access system views like DBA_2PC_PENDING.

How Does Oracle ROLLBACK work?

ROLLBACK in Oracle works as it asks the Oracle to rollback or omits the changes done by the current transaction if no savepoint clause is provided otherwise it rollbacks the changes done only till the savepoint. In other words it restores the database to the savepoint mentioned in the clause. Suppose there is insert and update statement in the current transaction and they are executed. If we do not need the changes made by the update statement then in that case we need to use ROLLBACK statement with a savepoint clause with it which would tell the database to omit the changes of the update statement and bring the database to the state which it was before UPDATE statement was executed but the changes made due to INSERT statement will be present. In case we do not use any savepoint and simply want to omit all the changes done by INSERT and DELETE statement then we simply use ROLLBACK statement without any clause and all the changes are omitted and database returns to the state which it was before the INSERT and UPDATE statement was executed.

Examples to Implement Oracle ROLLBACK

Below are the examples mentioned:

Example #1

ROLLBACK WITHOUT CLAUSE: In this case we are going to use ROLLBACK to just omit the changes done by current transaction. In this case we are going to insert a new row in the table EMPLOYEE and then we will use ROLLBACK statement to omit all the changes done by the insert statement. Let us look at the SQL statements for the same.

Code:

insert into employee values('Sunil','TATA','AD014','VH010','Kolkata','34','9878207095','AD008','65000');
ROLLBACK;

The first SQL statement inserts the values in the SQL statement into the employees table and then we will use the ROLLBACK statement to rollback the changes and make the database go to the state which was before the insert statement was applied.

Output:

As we can see in the above screenshot that after the insert query was executed, the ROLLBACK was successfully completed.

Now let us run the select query and check if the values are present in the Employee table.

As we can see that the Employee table does not have the values which we had inserted using the INSERT statement. Hence ROLLBACK statement successfully restores the database to its previous state before the current transaction.

Example #2

ROLLBACK WITH SAVEPOINT: In this case, we will not ROLLBACK the entire transaction but we will only rollback to a certain savepoint which we will declare in between the transaction using the SAVEPOINT statement. So, in this example we will first insert values in the wrong columns of the employee table using the INSERT statement and then create the insert statement as a SAVEPOINT and then update the wrong inserted values using the UPDATE statement. Let us look at the query.

Code:

insert into employee values('Sunil','TATA','AD014','VH010','Kolkata','34','9878207095','AD008','65000');
savepoint  trans_1;

In the above query as we can see there are two SQL statements one is the insert statement and the next one sets the savepoint.

Output:

As, we can see in the screenshot that the insert statement has been successfully executed and the savepoint has also being created.

Now let us run the SELECT statement to see the EMPLOYEE table.

Select * from employee;

If we see the row number 13 in the screenshot then we can see that the row has been inserted.

Let us now run the UPDATE statement to UPDATE the contents and then the ROLLBACK statement to bring the database to the savepoint trans_1.

UPDATE EMPLOYEE SET NAME ='SUNIL' WHERE VEHICLE_ID ='VH010';
ROLLBACK TO SAVEPOINT trans_1;

In this query, we are updating the name and then using ROLLBACK with the SAVEPOINT clause.

Let us run the query in SQL developer and check the result.

As we can see the ROLLBACK to SAVEPOINT has been completed successfully. Now let us run the SELECT statement to check the EMPLOYEE table.

As we can see the content of the EMPLOYEE table is same as it was in SAVEPOINT trans_1.

Conclusion

In this article, we discussed about the definition of ROLLBACK statement and the syntax. Later on in the article the working of ROLLBACK in Oracle and the various cases we used ROLLBACK along with appropriate examples were discussed for better understanding of the concept.

Recommended Articles

This is a guide to Oracle ROLLBACK. Here we discuss an introduction to Oracle ROLLBACK, syntax, parameters, how does it work, examples with code and output. You can also go through our other related articles to learn more –

1. Oracle vs SQL Server
2. Career in Oracle
3. Oracle vs MSSQL
4. Oracle Window Functions
5. How to Works Oracle While Loop?