oracle sql and dba tutorial logo

Tutorial for Oracle DBA

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions

42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions

Oracle SQL Tutorial Contents

Introduction to Databases

CODD'S Rules

Datatypes and Create Tables

Oracle SELECT Statement

Formatting in SQL*Plus

UNION, INTERSECT, MINUS Operators and Sorting Query Result

Oracle SQL Functions

Number Functions (Math Functions)

Character Functions

Miscellaneous Functions

Aggregate Functions

Date and Time Functions

Oracle Join Queries

GROUP BY Queries, SUB Queries

CUBE, ROLLUP Functions




Data Control Language (GRANT, REVOKE)



Integrity Constraints (PRIMARY KEY, NOT NULL...)


Dropping Constraints

Disabling and Enabling

Differing Constraints Check

View Info about Constraints

Working with Dates

Oracle Views

Oracle Sequences

Oracle Synonyms

Indexes and Clusters

Table Partitioning

Altering Partition Tables

Dropping Partitions

Merging Partitions

Splitting Partitions

Coalescing Partitions

Oracle Objects and Object Types










How to Rollback or Commit in Oracle

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.


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.


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




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 */



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


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. At this point 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. At this point 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


Then the whole transactions is roll backed.

If you give


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



HomeContact Us

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 E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved