Wednesday, 1 March 2017

doubt transactions in oracle

Query to check in doubt transactions...
=====================================

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mon-yyyy HH24:MI:SS'), STATE, MIXED FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID          GLOBAL_TRAN_ID                           TO_CHAR(FAIL_TIME,'DD-MON-YYY STATE            MIX
---------------------- ---------------------------------------- ----------------------------- ---------------- ---
88.33.88299            abc.a930e3e9.36.36.71299             24-jan-2017 07:56:31          collecting       no
44.77.88441            abc.a930e3e9.33.40.68441             24-jan-2017 09:27:49          collecting       no
99.99.99919            abc.a930e3e9.27.44.59919             24-jan-2017 15:39:21          collecting       no

SQL> SELECT LOCAL_TRAN_ID, IN_OUT,INTERFACE, DATABASE FROM DBA_2PC_NEIGHBORS;

LOCAL_TRAN_ID          IN_ I DATABASE
---------------------- --- - ------------------------------
01.01.11299            in  N
33.44.55441            in  N
22.44.55919            in  N
88.33.88299            out N REM2CRM_HZ_PARTIES_DBLINK
44.77.88441            out N REM2CRM_HZ_PARTIES_DBLINK
99.99.99919            out N REM2CRM_HZ_PARTIES_DBLINK

6 rows selected.

SQL>

To clear the in doubt transactions...
=====================================
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('88.33.88299');
COMMIT;

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('44.77.88441');
COMMIT;

No comments:

Post a Comment