Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Paul.

Asked: January 30, 2018 - 2:24 pm UTC

Last updated: February 01, 2018 - 3:13 am UTC

Version: 10.2.0.4.0

Viewed 1000+ times

You Asked

Is there a way to identify if the session executing the PL/SQL application is part of the XA Transaction.
We have an application running on WebLogic 12.2.1 using a JDBC Pool. It connects to the database through the Connection Pool.

Some of the relevant settings on the WebLogic for the JDBC Data Source are:
Use XA Data Source Interface: true
Keep XA Connection Until Transaction Complete: true
Keep Connection After Local Transaction: true
XA Retry Interval: 60
Driver Class Name: oracle.jdbc.xa.client.OracleXADataSource

Appreciate if you have a relatively efficient way to identify this. I suppose another angle would be to identify if the transaction (or session) is Non-XA as well.

and Connor said...

The best I can come up with is something like:

select username, osuser, status, sid, serial#, machine, process, terminal, program
from v$session
where saddr in (select k2gtdses from sys.x$k2gte );


which shows sessions currently involved in an XA transaction (x$k2gte is the object that sits under DBA_2PC_PENDING) but obviously this information will be fleeting.

One other avenue to explore is to check to see if V$SESSION_CONNECT_INFO has anything useful that you can use to isolate such sessions.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.