RE: dba_2pc_pending transaction_id to sql_text

From: Chitale, Hemant K <Hemant-K.Chitale_at_sc.com>
Date: Tue, 24 Feb 2015 08:55:38 +0000
Message-ID: <2FE2AA1C5F8DEC478F58DF8DD32BA63701B825_at_HKWPIPXMB03C.zone1.scb.net>



If V$TRANSACTION doesn't have entries, check STATE in DBA_2PC_PENDING. If it is COMMITTED or COMMIT FORCED or ROLLBACK FORCED, you should be able to simply purge it with DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY

Otherwise log an SR with Oracle Support for instructions to cleanup the transaction entry.

Hemant K Chitale

-----Original Message-----
From: Anton [mailto:djeday84_at_gmail.com] Sent: Tuesday, February 24, 2015 4:50 PM To: Chitale, Hemant K
Cc: ORACLE-L
Subject: Re: dba_2pc_pending transaction_id to sql_text

Chitale thx for reply, so if i have count of 30 in DBA_2PC_PENDING and count 0 in gv$transaction it is impossible to get failed query ?

On 02/24/2015 11:42 AM, Chitale, Hemant K wrote:
> The three elements of DBA_2PC_PENDING map to, I think, XIDUSN, XIDSLOT, XIDSQN in V$TRANSACTION.
>
> However, in this case, you should check the STATE in DBA_2PC_PENDING. The local transaction may or may not be present.
>
> Normally, for a transaction, I would join V$TRANSACTION to V$SESSION on T.ADDR=S.TADDR -- that doesn't guarantee that I can get the actual SQL for the transaction, only the session.
>
> Hemant K Chitale
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Anton
> Sent: Tuesday, February 24, 2015 3:50 PM
> To: oracle-l_at_freelists.org
> Subject: dba_2pc_pending transaction_id to sql_text
>
> Hello guru of oracle-l.
> How can i check what does transaction in dba_2pc_pending do, or how to get sql text by LOCAL_TRAN_ID in dba_2pc_pending ?
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
> This email and any attachments are confidential and may also be
> privileged. If you are not the intended recipient, please delete all
> copies and notify the sender immediately. You may wish to refer to the
> incorporation details of Standard Chartered PLC, Standard Chartered
> Bank and their subsidiaries at
> https://www.sc.com/en/incorporation-details.html

This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html i0zX+n{+i^ Received on Tue Feb 24 2015 - 09:55:38 CET

Original text of this message