Checking for Active Transactions

 Oracle 11.2 introduced the WAIT_ON_PENDING_DML function in the DBMS_UTILITY Package.

Here is a demonstration of how to use it (with anonymous PL/SQL blocks, instead of a Stored Procedure):

17:05:21 SQL> @Check_for_Transactions
17:05:22 SQL> declare
17:05:22   2  check_for_transactions boolean;
17:05:22   3  scnvalue number;
17:05:22   4  begin
17:05:22   5  check_for_transactions := dbms_utility.wait_on_pending_dml
17:05:22   6                            (tables=>'HEMANT.MY_TXN_TABLE',
17:05:22   7                             timeout=>60,
17:05:22   8                             scn=>scnvalue);
17:05:22   9  if check_for_transactions then
17:05:22  10    dbms_output.put_line('No Transaction(s) at start time OR Transactions present at start have committed before Timeout');
17:05:22  11    dbms_output.put_line('Other Transactions *may* have begun after the start of this Check');
17:05:22  12  else
17:05:22  13   dbms_output.put_line('One or More Active Transaction(s) present until Timeout');
17:05:22  14  end if;
17:05:22  15  end;
17:05:22  16  /
No Transaction(s) at start time OR Transactions present at start have committed before Timeout
Other Transactions *may* have begun after the start of this Check

PL/SQL procedure successfully completed.

17:05:22 SQL>


17:05:43 SQL> @Check_for_Transactions
17:05:44 SQL> declare
17:05:44   2  check_for_transactions boolean;
17:05:44   3  scnvalue number;
17:05:44   4  begin
17:05:44   5  check_for_transactions := dbms_utility.wait_on_pending_dml
17:05:44   6                            (tables=>'HEMANT.MY_TXN_TABLE',
17:05:44   7                             timeout=>60,
17:05:44   8                             scn=>scnvalue);
17:05:44   9  if check_for_transactions then
17:05:44  10    dbms_output.put_line('No Transaction(s) at start time OR Transactions present at start have committed before Timeout');
17:05:44  11    dbms_output.put_line('Other Transactions *may* have begun after the start of this Check');
17:05:44  12  else
17:05:44  13   dbms_output.put_line('One or More Active Transaction(s) present until Timeout');
17:05:44  14  end if;
17:05:44  15  end;
17:05:44  16  /
One or More Active Transaction(s) present until Timeout

PL/SQL procedure successfully completed.

17:06:44 SQL>


17:07:08 SQL> @Check_for_Transactions
17:07:09 SQL> declare
17:07:09   2  check_for_transactions boolean;
17:07:09   3  scnvalue number;
17:07:09   4  begin
17:07:09   5  check_for_transactions := dbms_utility.wait_on_pending_dml
17:07:09   6                            (tables=>'HEMANT.MY_TXN_TABLE',
17:07:09   7                             timeout=>60,
17:07:09   8                             scn=>scnvalue);
17:07:09   9  if check_for_transactions then
17:07:09  10    dbms_output.put_line('No Transaction(s) at start time OR Transactions present at start have committed before Timeout');
17:07:09  11    dbms_output.put_line('Other Transactions *may* have begun after the start of this Check');
17:07:09  12  else
17:07:09  13   dbms_output.put_line('One or More Active Transaction(s) present until Timeout');
17:07:09  14  end if;
17:07:09  15  end;
17:07:09  16  /
No Transaction(s) at start time OR Transactions present at start have committed before Timeout
Other Transactions *may* have begun after the start of this Check

PL/SQL procedure successfully completed.

17:07:23 SQL>

When I ran the Check code at17:05:22, there were no active transaction against the target table "HEMANT.MY_TXN_TABLE", so the Check completed immediately (the returned BOOLEAN is TRUE)

When I re-ran the Check code at 17:05:44, there were one or more transactions (uncommitted) present.  The Check code ran for 60 seconds until the specified timeout and returned the message "One or More Active Transaction(s) present until Timeout"

When I ran the Check code again at 17:07:09 there were one or more transactions present.  However, they committed within the 60seconds timeout so the Check ended at 17:07:23  (i.e. the function returned TRUE at 17:07:23)

However, this Check only checks for transactions present as at the time it began running.  If a third or fourth session begins a transaction after this start and yet does not commit, it would not be identified by this Check.  

With the caveat that the Check doesn't check for *new* transactions, this is useful when you are monitoring for the presence of transactions at a specific time --- .e.g you expected an ETL job to complete by 17:30 and know that no other session would have any transaction against the target table.

The "TABLES" parameter can actually take a comma-separated list of tables.

The "SCN" parameter is an IN OUT in that you can put in a specific SCN prior to which transactions may begun.  If a NULL or invalid value is passed, the function takes the current SCN.