sql定位过程报错_使用10046事件跟踪sql定位错误一例

修改一个表时，发生如下错误：

SQL> update emp set ename='adsf';

update emp set ename='adsf'

*

ERROR at line 1:

ORA-12096: error in materialized view log on "SCOTT"."EMP"

ORA-00942: table or view does not exist

这个提示看上去不太直观，好像是提示在物化视图日志不存在，查看一下该用户下的表：

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

ASDF                           TABLE

BONUS                          TABLE

DEPT                           TABLE

EMP                            TABLE

EMPLOYEES                      TABLE

LOB_TEST                       TABLE

MLOG$_BONUS                    TABLE

MLOG$_SALGRADE                 TABLE

MLOG$_T1                       TABLE

MV_T                           TABLE

PLAN_TABLE                     TABLE

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

RUPD$_BONUS                    TABLE

RUPD$_DEPT                     TABLE

RUPD$_EMP                      TABLE

RUPD$_SALGRADE                 TABLE

RUPD$_T1                       TABLE

SALES_COMPOSITE                TABLE

SALES_HASH                     TABLE

SALES_RANGE                    TABLE

SALGRADE                       TABLE

T                              TABLE

T1                             TABLE

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

TTT                            TABLE

T_T                            TABLE

V_EMP                          VIEW

V_T                            TABLE

26 rows selected.

并没有物化视图日志表MLOG$_EMP，估计有可能是在删除物化视图日志表时，直接使用了drop table MLOG$_EMP，而没有使用DROP MATERIALIZED VIEW LOG ON EMP，这就导致oracle在更新emp表时，试图去更改物化视图日志表，但发现物化视图日志表已经被删除，于是报错。为了证明上述猜想，用10046事件进行trace，得到如下trace内容：

=====================

PARSING IN CURSOR #1 len=27 dep=0 uid=21 ct=6 lid=21 tim=1198342181468910 hv=4011695554 ad='58aec934'

update emp set ename='adsf'

END OF STMT

PARSE #1:c=10000,e=2463,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1198342181468899

BINDS #1:

=====================

PARSE ERROR #2:len=168 dep=1 uid=0 ct=2 lid=0 tim=1198342181470293 err=942INSERT /*+ IDX(0) */ INTO "SCOTT"."MLOG$_EMP" (dmltype$$,old_new$$,snaptime$$,change_vector$$) VALUES (:d,:o,to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c)EXEC #1:c=0,e=3846,p=0,cr=3,cu=4,mis=0,r=0,dep=0,og=4,tim=1198342181472888

ERROR #1:err=12096 tim=2451155094

WAIT #1: nam='log file sync' ela= 34783 p1=750 p2=0 p3=0

WAIT #1: nam='SQL*Net break/reset to client' ela= 19 p1=1650815232 p2=1 p3=0

WAIT #1: nam='SQL*Net break/reset to client' ela= 135 p1=1650815232 p2=0 p3=0

WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0

*** 2008-11-19 08:52:04.253

WAIT #1: nam='SQL*Net message from client' ela= 322645203 p1=1650815232 p2=1 p3=0

STAT #1 id=1 cnt=0 pid=0 pos=1 bj=0 p='UPDATE  (cr=0 r=0 w=0 time=0 us)'

=====================

可以看出，红色字体部分正是导致出错的sql。

执行DROP MATERIALIZED VIEW LOG ON EMP，再进行update。

SQL> drop MATERIALIZED VIEW LOG ON emp;

Materialized view log dropped.

SQL> update emp set ename='adsf';

17 rows updated.

更新成功。