13 June, 2014

Getting your Transaction ID

You can get the Transaction ID for a session by joining V$SESSION.TADDR to V$TRANSACTION.ADDR.

A Transaction ID consists of the Undo Segment #, the Slot # and the SEQ #.

For example :
SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
6.3.9463

SQL>

Thus, my current transaction is in Undo Segment 6, Slot 3, Sequence 9463.
SQL>    select count(*) from v$transaction;

  COUNT(*)
----------
         1

SQL> col username format a12
SQL> l
  1  select s.username, s.sid, s.serial#,
  2  t.xidusn, t.xidslot, t.xidsqn
  3  from v$session s, v$transaction t
  4* where s.taddr=t.addr
SQL> /

USERNAME            SID    SERIAL#     XIDUSN    XIDSLOT     XIDSQN
------------ ---------- ---------- ---------- ---------- ----------
HEMANT               38         23          6          3       9463

SQL> 

As soon as I commit or rollback or issue a DDL or a CONNECT, the transaction ends.
SQL> rollback;

Rollback complete.

SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         0

SQL> 

Remember that an Undo segment can hold multiple transactions. That is why the Undo Segment Header has multiple "slots", one for each active transaction.  Once a transaction completes (and likely after the undo_retention period), a slot can be reused with an incremented seq#.
.
.
.

No comments: