How to get the Oracle transaction id of the transaction that caused a trigger

Question

Let's say I have a table called "user". I have a trigger that fires on an insert into the "user" table. Is there a way to get the transaction id of the insert inside of the trigger?

Answer 1

You can use the function dbms_transaction.local_transaction_id

Here is an example: (taken from here)

SELECT dbms_transaction.local_transaction_id
FROM dual;

CREATE TABLE t (
testcol NUMBER(3));

INSERT INTO t
VALUES (1);

SELECT dbms_transaction.local_transaction_id
FROM dual;

CREATE OR REPLACE FUNCTION atf RETURN VARCHAR2 IS
 PRAGMA AUTONOMOUS_TRANSACTION;
 x VARCHAR2(20);
BEGIN
  INSERT INTO t
  (testcol)
  VALUES
  (2);

  x := dbms_transaction.local_transaction_id;
  COMMIT;

  RETURN x;
END atf;
/

set serveroutput on

DECLARE
 x VARCHAR2(20);
BEGIN
  x := atf;
  dbms_output.put_line(x);

  INSERT INTO t VALUES (3);

  x := dbms_transaction.local_transaction_id;
  dbms_output.put_line(x);
  COMMIT;
END;
/

Answer 2

When using Oracle, you have to execute the following SQL query:

SELECT tx.xid
FROM v$transaction tx
JOIN v$session s ON tx.ses_addr = s.saddr

The v$transaction view provides information about the currently running database transactions. However, there can be multiple transactions running in our system, and that’s why we are joining the v$transaction with the v$session view.

The v$session view offers information about our current session or database connection. By matching the session address between the v$transaction and v$session views, we can find the current running transaction identifier given by the xid column in the v$transaction view.

Note that, Oracle assigns a transaction identifier only if it needs to assign an undo segment, which implies that an INSERT, UPDATE or DELETE DML statement has been executed.

So, read-only transactions will not have a transaction identifier assigned.