Re: How to get session ID and sql_id based on these data

Thanks Dominic for you answer.

I will check if the transaction is still there and I think that the best way to solve it is to apply :

ROLLBACK FORCE '4.33.1741612'; ​Regards
Eriovaldo
​

2017-06-14 15:24 GMT-03:00 Dominic Brooks <dombrooks_at_hotmail.com>:

> 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 <ecandrietta_at_gmail.com>
> *Sent: *14 June 2017 18:44
> *To: *ORACLE-L <oracle-l_at_freelists.org>
> *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.00000000000000000000FFFF0A1408
> 20E9268D1A5922E2CF1D0FA15631 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
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l