«Back to Home

Oracle Jump Start
Topics

How To Use Commit and Rollback Command in Oracle

Introduction
 
In this article, you will learn, how to use commit and rollback command in Oracle with the syntax and the examples.
  • Commit command is used to make changes to the data (insert, updates, deletes) permanent.
  • Rollback command is used to discard the parts or all the work; the user has done in the current transaction.
  • Save points statements are used to discard or commit all the changes up to a point.
The changes made by the user are not physically written to the table, giving only the user; a view of user work, while the other users, having access to these tables, continue to get the old information.

Commit command is used to makes the changes permanent to the database. The user can continue with any number to insert, updates, deletes and still can undo the work, using Rollback command.

SQL * plus has the facility to automatically commit all the work, without explicitly, using Commit command.

   SET AUTO COMMIT ON: Enable auto commit feature.
   SET AUTO COMMIT OFF: Is the default and disable the automatic committing.
 
Implicit commit
 
The actions, which will force a commit to occur, even without, using Commit command are existing in SQL * plus, which helps in creating any object, granting or revoking resources, altering objects, connecting or disconnecting from Oracle.
  1. Select * from Customer; 
1 
 
Example
  1. Update customer set customer_name = ‘scott’ where customer_id = 105;  
  2. Select * from customer;  
  3. Commit;  
2 
 
Rollback
 
After completion of any Data Manipulation Language (DML) statement, if the user experiences such as hardware failure and no explicit commit made, Oracle will automatically Rollback all un-committed work.
 
If the machine or database goes down, it cleans up the work; the next time the database is brought up.
 
Example 
  1. Select * from customer;  
3
 
  1. Insert Into customer    
  2. (Customer_id, Customer_name, Product_code, Quantity)    
  3. values    
  4. (109, ‘Tiger’, ‘AB1’, 20);   
4
  1. ROLLBACK;  
  2.   
  3. Select * from customer;  
5 
 
Save points
 
Save point identifies a point in a transaction, which one can later rollback with Rollback statement. It is helpful, when a transaction contains a large number of SQL statements and the user wants to commit only once, when all are done. If required, one can roll back to a particular transaction. It works in a Last-In-First-out (LIFO) manner.
 
Example 
  1. Select * from customer;    
6
 
  1. Insert Into customer    
  2. (Customer_id, Customer_name, Product_code, Quantity)    
  3. values    
  4. (110, ‘Roy’, ‘DC1’, 10);   
  1. Savepoint s1;  
7 
  1. update customer set customer_id = 109 where customer_name = 'Roy';  
8
  1. Rollback to savepoint s1;  
9
Summary
 
Thus, we learnt, Commit command is used to makes the changes permanent to the database. Rollback command is used to discard parts or all the work; the user has done in the current transaction. We learnt, how to use these commands with the examples in Oracle.