CODEWITHSUNDEEP
oracle-databaseplsqltransactions
ceving
ceving

Reputation: 23876

How to get the name of a named transaction in Oracle?

I would like to use the name of a transaction in a trigger in order to write it into a column.

I tried this (in SQL Developer):

set transaction name 'hello';
select DBMS_TRANSACTION.LOCAL_TRANSACTION_ID from dual;
commit;

But instead of the name of the transaction I get some kind of random value:

transaction NAME succeeded.
SUBSTR(DBMS_TRANSACTION.LOCAL_TRANSACTION_ID,0,20)
--------------------------------------------------
1.25.19794                                         

committed.

How can I get the name of a named transaction?

Upvotes: 2

Views: 2111

Answers (2)

Ed Gibbs
Ed Gibbs

Reputation: 26363

I just tried something similar, and for the DBMS_TRANSACTION.LOCAL_TRANSACTION_ID I got back the value 9.7.1270. When I looked up the transaction by name, I found the following:

  • v$transaction.XIDUSN = 9
  • v$transaction.XIDSLOT = 7
  • v$transaction.XIDSQN = 1270

Put them together and you get the 9.7.1270. So (and note that this could be wrong - the docs I found don't cover this), you may be able to get the current transaction name like this:

SELECT Name
FROM v$transaction
WHERE xidusn ||'.'|| xidslot ||'.'|| xidsqn = DBMS_TRANSACTION.LOCAL_TRANSACTION_ID;

One fact I could establish: the v$transaction row won't show up until you do one of the following:

  1. An INSERT/UPDATE/DELETE/MERGE type of operation, or
  2. Call DBMS_TRANSACTION.LOCAL_TRANSACTION_ID on its own - meaning that just having it in the query above doesn't seem to be enough to populate the v$transaction row.

But if I do either of the above, the query works (I've tested it 4 or 5 times now), and since you'll be trying it in a trigger you should be covered by item #2 above.

One final note: v$transaction access is fairly restricted, so most users won't be able to see it. Complicating matters, v$transaction isn't the actual view; it's a synonym for the view sys.v_$transaction (note the underscore before the dollar sign) so that's the name you need to use when granting. And I'm pretty sure you have to be logged in as SYSDBA to do the grant - I was able to query v$transaction as the SYSTEM user but I didn't have sufficient privileges to GRANT SELECT to another user.

Upvotes: 3

tbone
tbone

Reputation: 15483

I think you're looking for v$transaction. For example:

set serveroutput on
set echo on
set transaction name 'test1_txn';
update my_table set dte = sysdate;
-- this will show the named transaction
select * from v$transaction where name = 'test1_txn';
commit;
-- after commit, won't show
select * from v$transaction where name = 'test1_txn';

More detail here

Upvotes: 1

Related Questions