Distributed transactions don’t need a permanent session. A session can
attach/detach from a transaction.
See third part here:
https://orastory.wordpress.com/2013/01/15/fun-with-distributed-transactions/
On the 25th, the transaction “prepared” to commit and then the co-ordinator
never returned to do the second phase.
So it needs to be cleaned up – either committed or rolled back (whatever you
deem appropriate) – and then purge it.
https://docs.oracle.com/database/121/ADMIN/ds_txnman.htm#ADMIN-GUID-94CB1DFF-1100-4D3B-8E83-902CA85B5551
We get this quite a lot on our application, normally when someone/thing does a
kill -9 on the java application server which is the transaction co-ordinator...
normally causes significant issues but doesn’t stop them / the scripts from
doing it.
Regards,
Dominic
Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10
From: Eriovaldo Andrietta<mailto:ecandrietta@xxxxxxxxx>
Sent: 14 June 2017 18:44
To: ORACLE-L<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: How to get session ID and sql_id based on these data
Hello,
I am facing a error : ORA-01591: lock held by in-doubt distributed transaction
string
I have a transaction in the table dba_2pc_pending, retrivied with the statment
below:
select * from dba_2pc_pending;
LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIXED ADVICE
TRAN_COMMENT FAIL_TIME FORCE_TIME RETRY_TIME OS_USER OS_TERMINAL
HOST DB_USER COMMIT#
17 4.33.1741612
131077.00000000000000000000FFFF0A140820E9268D1A5922E2CF1D0FA15631 prepared
no 25/05/2017 19:05:51 13/06/2017 20:13:21 sssprd1
unknown lnx-prd-01.go 11342881924682
I also executed this statment :
SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk, b.status
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
order by b.used_urec;
but I did not saw any information related to the pending in the dba_2pc_pending.
How can I get the session ID and the sql_id that is pending ?
Regards
Eriovaldo
