Hello,
Context: we need to add tables for some auditing purpose on a group of critical tables. To correlate changes over these different tables when extracting data, we want to use the transaction id with other key columns.
I searched a lot on google, inside the database and inside the docs on how to retrieve the current transaction id of a session.
What I've found from the docs is dbms_transaction.local_transaction_id.
It returns 3 concatenated numbers, seperated by dots.
These ones seem to come from v$transation.{xidusn,xidslot,xidsqn}
Example:
SQL> SELECT dbms_transaction.local_transaction_id, xidusn, xidslot, xidsqn, xid
FROM V$TRANSACTION
LOCAL_TRANSACTION_ID XIDUSN XIDSLOT XIDSQN XID
-------------------- ---------- ---------- ---------- --------------------------------
12.17.27487 12 17 27487 000C001100006B5F
So, is the local_transaction safe under RAC?
What is the difference with XID from v$transaction, that is a raw(8) datatype?
We can also found this xid column under many other tables is sys schema. Ex: dba_audit_trail.TRANSACTIONID
And we can also found other XID column with a varchar2(30) data type (ex: some ENQ_TXN_ID on AQ tables), why?
Is the raw XID better than LOCAL_TRANSACTION_ID? If yes, how to obtain it?
If I use LOCAL_TRANSACTION_ID, what is the max length? 30? Even if the XIDUSN, XIDSLOT and XIDSQN are all unlimited length NUMBERs?
What I need, is the best and safest, correct way.
More info on our setup:
- we are in RAC
- we use 10gR2
Thanks
Bruno