Pending XA Transactions on Oracle DB

With the statement:

SELECT formatid, globalid, branchid FROM SYS.DBA_PENDING_TRANSACTIONS;
select * from DBA_2PC_PENDING;
select * from V$GLOBAL_TRANSACTION


you should be able to extract info on the pending XA transactions.

This is an excellent document on the parameters regulating the timeout on Oracle DB with XA transactions


More interesting reading here:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_xa.htm


two-phase commit = prepare + commit, then forget


DEFAULT_XA_TIMEOUT = 60
XA_SETTIMEOUT defaults to 60
DISTRIBUTED_LOCK_TIMEOUT specifies the amount of time (in seconds) for distributed transactions to wait for locked resources.


Generally, these timeouts should be set in the following order:
1. TM’s global transaction timeout (or the JTA timeout) <
2. SesTm and SesWt (Transaction Timeout for JDBC or PL/SQL) <
3. DISTRIBUTED_LOCK_TIMEOUT <
4. Either of CONNECT_TIME or IDLE_TIME