此案例在数据字典视图 DBA_2PC_PENDING 中存在一个不确定的分布式事务。如何找到发起此事务的用户以及事务的 SQL 语句。

通过DBA_2PC_PENDING 的对应的 COMMIT#和 事务ID，结合Logminer 来检查事务所在的在线重做日志或归档重做日志，以确定用户名和 SQL 文本。具体示例如下：

SQL> show user
USER is "SYS"
SQL> select local_tran_id, commit#, state
2 from DBA_2PC_PENDING;

LOCAL_TRAN_ID COMMIT# STATE
---------------------- ---------------- ----------------
1.16.942 1585840 prepared

使用 COMMIT#，检查事务是否在在线重做日志中（查询 V$LOG 和 V$LOGFILE）或在归档重做日志中（查询 V$ARCHIVED_LOG）。

Online Redo:

SQL> select a.thread#, a.sequence#, b.member
2 from v$log a, v$logfile b
3 where a.group# = b.group# and
4 1585840 between a.FIRST_CHANGE# and a.NEXT_CHANGE#;

THREAD#     SEQUENCE#       MEMBER

----------  ----------      --------
1           55             /u01/oradata/test/redo01.log

Archived Redo:

SQL> select thread#, sequence#, name
2 from v$archived_log
3 where 1585840 between FIRST_CHANGE# and NEXT_CHANGE#;

THREAD#    SEQUENCE#        NAME

---------- ----------       --------
1          55              /u01/arch/arch1_55_937607111.dbf

使用Logminer进行挖掘：

SQL> show user
USER is "SYS"
SQL> exec dbms_logmnr.add_logfile(' /u01 /arch/arch1_55_937607111.dbf');

PL/SQL procedure successfully completed.

使用 DBA_2PC_PENDING查询 local_tran_id带入查询

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR (OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

SQL> select OPERATION, TABLE_NAME, SQL_REDO, USERNAME
2 from V$LOGMNR_CONTENTS
3 where XIDUSN=1 and
4 XIDSLT = 16 and
5 XIDSQN=942 order by SCN asc;

OPERATION TABLE_NAME
-------------------------------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
USERNAME
------------------------------
START
set transaction read write;
SCOTT

INSERT EMP
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('1041','MULDER',NULL,NULL,NULL,NULL,NULL,'10');

SCOTT

INTERNAL

SCOTT

INTERNAL

SCOTT

SQL>

最终定位事务1.16.942是由SCOTT用户执行了EMP表的insert操作