Oracle DBA Tutorial 
Oracle DBA
Learn Oracle 11g Database Admin step by step

 

 

Oracle SQL
Tutorial for Oracle SQL

 

Oracle DBA Interview Questions
Most asked Oracle DBA Interview Questions.

Technical 60 Questions

Backup &
Recovery 42 Questions


Unix For Oracle
DBA 20 Questions

 

 

Download Oracle 11g Software
Links to Download Oracle 11g for Linux, Windows etc.

 




How to COMMIT, ROLLBACK  Oracle Transactions

Oracle Database Administration
Learn Oracle 11g database administration
step by step

 

Oracle DBA Interview Questions
Frequently asked Oracle DBA Interview
Questions

 

Download Oracle 11g Software
Links for downloading Oracle 11g software
Installation guides for installing Oracle under Linux, Solaris

Transaction Control Language (TCL)

Transaction control statements manage changes made by DML statements.

What is a Transaction?

A transaction is a set of SQL statements which Oracle treats as a Single Unit. i.e. all the statements should execute successfully or none of the statements should execute.

To control transactions Oracle does not made permanent any DML statements unless you commit it. If you don’t commit the transaction and power goes off or system crashes then the transaction is roll backed.

TCL Statements available in Oracle are

COMMIT       :Make changes done in  transaction permanent.

ROLLBACK  :Rollbacks the state of database to the last commit point.

SAVEPOINT :Use to specify a point in transaction to which later you can rollback.

COMMIT

To make the changes done in a transaction permanent issue the COMMIT statement.

The syntax of COMMIT Statement is

COMMIT  [WORK]  [COMMENT ‘your comment’];

WORK is optional.

COMMENT is also optional, specify this if you want to identify this transaction in data dictionary DBA_2PC_PENDING.

Example

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

commit;

 

ROLLBACK

 

To rollback the changes done in a transaction give rollback statement. Rollback restore the state of the database to the last commit point.

 

 

Example :

delete from emp;

rollback;          /* undo the changes */

 

 

SAVEPOINT

 

Specify a point in a transaction to which later you can roll back.

 

Example

 

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

savepoint a;

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

savepoint b;

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

 

Now if you give

 

rollback to a;

 

Then  row 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.

 

If you give

 

commit;

 

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

 



Convert MS SQL to MySQL
Convert MS Access to MySQL
Convert MS Access to MSSQL
Convert CSV to MySQL
Convert Foxpro to MySQL

Convert Foxpro to MSSQL
Convert MS Access to Oracle
Convert MSSQL to Oracle
Convert CSV to Oracle

Data Loader
Data Loader is a simple yet powerful tool to export and import Data between many common database formats
Forms Data Loader
Tool to load data into Oracle Applications 11i / R12 using Macros and Forms Record and Playback