Transactions in Oracle

  A transaction is a set of SQL statements which Oracle treats as a Single Unit. Either all the statements should execute successfully or none. ( No partial commits )

TCL (Transaction control language) commands are used for controlling the state of the transaction they are fired from. 

TCL Statements available in Oracle are:

COMMIT :  Makes changes done in  a transaction permanent.

Example :

insert into emp (empno,ename,sal) values (101,’Abid’,2300);

commit;

ROLLBACK : used to rollback the changes and restore the state of database to the last commit point.

Example :

delete from emp;

rollback;          /* undo the changes */

SAVEPOINT

Used to specify a point in the transaction to which later you can rollback. This is used for partial rollback.

Example :

insert into emp (empno,ename,sal) values (109,’Sami’,3000);

savepoint a;

insert into dept values (10,’Sales’,’TRV’);

savepoint b;

insert into salgrade values (‘III’,9000,12000);

Now if you give

rollback to a;

Then  rows from salgrade table and dept will be roll backed. Now you can commit the row inserted into emp table or rollback the transaction.

If you give

rollback to b;

Then row inserted into salgrade table will be roll backed. Now you can commit the row inserted into dept table and emp table or rollback to savepoint a or completely roll backed the transaction.

If you give

rollback; 

Then the whole transactions is roll backed. 

Note : You can rollback only to savepoints marked in the current txn.

If you give

commit;

Then the whole transaction is committed and all savepoints are removed.