Until Oracle 8i any Oracle session can handle only a single transaction at any given point of time. If a transaction “A” is started, no other transaction can be executed until the transaction “A” is commited or Rollbacked. But by the Autonomous transaction feature provided by Oracle, another transaction “B” can be completed suspending the Transaction “A”  and resuming it after the completion of transaction “B”.

Transaction “A” can be termed as main transaction. Transaction “B” can be termed as Autonomous transaction.

An Autonomous transaction is executed as independent and out of the scope of main transaction.An autonomous transaction is a transaction that is started with in context of another transaction. An autonomous transaction can be committed or rolled back regardless of the main transaction.

An Autonomous transaction has to be defined in a  Pl/sql block other than the block where main transaction is executed. You can define any Pl/sql block  like anonymous block, procedure, function, package procedure, package function, database trigger as an autonomous transaction.

To define a Pl/sql block as an autonomous transaction , you can use the statement below in the declaration section of a PL/SQL block.

PRAGMA AUTONOMOUS_TRANSACTION;

You can put the autonomous transaction pragma anywhere in the declaration section of PL/SQL block. The above PRAGMA directs the pl/sql compiler to consider this block as an  AUTONOMOUS TRANSACTION block.

Application of Autonomous transaction:

1. Autonomous transaction can be used  as a logging mechanism to log the transaction specific data.
2. Autonomous transaction can be used to record the transaction state for a successful transaction or a failed transaction.
3.To implement commit and rollback mechanisms in database triggers.
4.Autonomous transaction can be used to develop standalone units of work. So Autonomous transaction  can be helpful in building reusable components or reusable Modules.

Rules and Restrictions of using Autonomous transaction:

1. Only top level anonymous blocks can be declared as Autonomous transaction.
An example of Nested anonymous block declared as Autonomous transaction giving an error.

2. A deadlock can occur whenever an autonomous transaction tries to access a resourced which is held by the main transaction.

Ex:

CREATE OR REPLACE PROCEDURE
  update_salary (dept_in IN NUMBER)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
CURSOR myemps IS
  SELECT empno FROM emp
  WHERE deptno = dept_in
  FOR UPDATE NOWAIT;
BEGIN
  FOR rec IN myemps
  LOOP
  UPDATE emp SET sal = sal * 2
  WHERE empno = rec.empno;
  END LOOP;
  COMMIT;
END;
/
BEGIN
  UPDATE emp SET sal = sal * 2;
  update_salary (10);
END;
/

his results in the following error:

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

3. You cannot declare an entire package as an Autonomous with Single Pragma.

CREATE OR REPLACE package exp3 as
PRAGMA AUTONOMOUS_TRANSACTION;
PROCEDURE X;
PROCEDURE Y;
END ;
/

4. Every Autonomous transaction should end with an either commit or ROLLBACK statements.If an autonomous block ends with out completing a transaction ORA-6519 is raised.

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert into tx_tab(num) values(2);
end;
/Error at line 1: