Autonomous transactions benefits

An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions do SQL operations and commit or Rollback, without committing or rolling back the main transaction. For example, if you write auditing data to a log table, you want to commit the audit data even if the operation you are auditing later fails; if something goes wrong recording the audit data, you do not want the main operation to be rolled back.

For better understanding consider a transaction without Autonomous transaction first:

Both the transactions i.e insert on emp table as well as logs are rolled back.                                  Now consider I attempted to commit transaction on logs table and tried to rollback parent transaction i.e insert on emp.

So I created a trigger with pragma Autonomous_transaction

Record inserted into both the tables emp and logs

There after I rolled back

Here main transaction gets rolled back i.e insert on emp table rolled back

Child transaction is executing independently even if transaction on emp rolled back.

Author Profile

Tejas

Passionate traveller,Reviewer of restaurants and bars,tech lover,everything about data processing,analyzing,SQL,PLSQL,pig,hive,zookeeper,mahout,kafka,neo4j

Latest Post by this Author

• PLSQLApril 26, 2020How effectively we can use temporary tables in Oracle?
• Big DataAugust 15, 2019How to analyze hadoop cluster?
• Big DataJuly 28, 2019How to setup Hadoop cluster using cloudera vm?
• Big DataMay 25, 2019How to configure parameters in Hadoop cluster?