Search My Oracle Blog

Custom Search

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:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016