ANBOB™

早接到电话说从sqlserver同步过来的数据在oracle 应用没查到，我查询同步job发现失败了，job所调用的procedure 生成了错误编号ORA-02291
procedre中代码较多，决定用errorstack快速定位一下sql

操作如下:

--手动执行一下
SQL> exec  topbox.TOPBOX_ADD_VALUE;

PL/SQL procedure successfully completed.

查询procedure 生成的日志，写在procedure中insert 到一个表中
TOPBOX_ADD_VALUE	-2291	ORA-02291: integrity constraint (TOPBOX.FK_TOPBOX_CSTUDY_REF_COUSE) violated - parent key not found	2011-11-1 10:37:13

SQL> alter session set events '2291 trace name errorstack forever,level 1';

Session altered.

SQL> exec  topbox.TOPBOX_ADD_VALUE;

PL/SQL procedure successfully completed.

SQL> alter session set events '2291 trace name errorstack off';

Session altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/oracle/admin/topbox/udump/topbox_ora_9182.trc
SQL> host
[oracle@topbox ~]$ vi /oracle/admin/topbox/udump/topbox_ora_9182.trc


*** ACTION NAME:() 2011-11-01 10:37:12.732
*** MODULE NAME:(sqlplus@topbox (TNS V1-V3)) 2011-11-01 10:37:12.732
*** SERVICE NAME:(SYS$USERS) 2011-11-01 10:37:12.732
*** SESSION ID:(1965.55145) 2011-11-01 10:37:12.732
*** 2011-11-01 10:37:12.732
ksedmp: internal or fatal error
ORA-02291: integrity constraint (TOPBOX.FK_TOPBOX_CSTUDY_REF_COUSE) violated - parent key not found 
Current SQL statement for this session:
INSERT INTO TOPBOX_COURSESTUDY (ID, ICNUM, STUDYTIME, UPLOADTIME, STATE, COURSEWARE_ID, TOPBOXCODE) SELECT TOPBOX_COURSESTUDY_SEQ.NEXTVAL AS ID, ICNUM, STUDYTIME, UPLOADTIME, STATE
, COURSEWARE_ID, TOPBOXCODE FROM TOPBOX_ADD_COURSESTUDY AC WHERE EXISTS (SELECT 1 FROM TOPBOX_USERINFO UI WHERE AC.ICNUM = UI.ICNUM)
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x278ebcb80        47  procedure TOPBOX.TOPBOX_ADD_VALUE
0x28469e168         1  anonymous block 


提示是procedure TOPBOX.TOPBOX_ADD_VALUE 的47行，找出这段代码单独执行，很快发现了原因，因一字段错误，从sql server同步过来时少同步了课件信息，但有学该课程的学习记录无法对应出错

打赏

微信扫一扫，打赏作者吧～