Example of AUTONOMOUS_TRANSACTION

Example of how AUTONOMOUS_TRANSACTION, it allows one to isolate a DML statements, the DML statements with the AUTONOMOUS transaction are independent of the main transaction. AUTONOMOUS_TRANSACTION can be used in PL/SQL code in procedures, functions, triggers. -- store procedure that writes message to a log table and defined AUTONOMOUS_TRANSACTION SQL> CREATE OR REPLACE PROCEDURE scott.write_log(v_msg…

Example of SAVEPOINT and “ROLLBACK TO..”

Examples of using “SAVEPOINT” and “ROLLBACK TO” SQL> select * from scott.test; no rows selected SQL> insert into scott.test values (1, 2); 1 row created. SQL> savepoint trans_1; Savepoint created. SQL> insert into scott.test values (3, 4); 1 row created. SQL> savepoint trans_2; Savepoint created. SQL> insert into scott.test values (5, 6); 1 row created.…

How to find blocking session?

In 10g, there is a column in V$session called blocking_session to find the session id that is blocking. Session 1) sid # 145 SQL> select userenv(‘sid’) from dual; USERENV(‘SID’) ————– 145 SQL> create table t ( x number ); Table created. SQL> create unique index t_idx on t(x); Index created. SQL> insert into t values…