在实际应用开发中，我们有很多精力倾注在调试程序和异常情况的排查上。使用设计逻辑和场景开发的代码，经常被生产数据一次次的“攻破”。快速的定位错误数据和报错语句是我们提高工作效率的关键。本篇介绍使用Oracle的error stack方法来定位程序包中报错的SQL和取值。
1、场景展现
我们选择Oracle 11g来进行试验。
SQL> select * from v$version where rownum<3;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 – Production
数据表T包括object_id和object_name列。其中object_id为主键列。
SQL> create table t (object_id number, object_name varchar2(100));
Table created
SQL> alter table t add constraint pk_t primary key (object_id);
Table altered
灌入一部分基础数据，作为原始值。
SQL> insert into t select object_id, object_name from dba_objects where wner='SCOTT';
20 rows inserted
SQL> commit;
Commit complete
为了进行实验，我们创建一个procedure程序段，从dba_objects中选取数据插入到数据表T中。程序定义如下：
SQL> create or replace procedure P_ERRORSTACK_TEST
2 is
3 type t_objs_list is table of dba_objects%rowtype index by binary_integer;
4 t_obj_infos t_objs_list;
5 begin
6 select *
7 bulk collect into t_obj_infos
8 from dba_objects;
9
10 if (t_obj_infos.count<>0) then
11 for i in t_obj_infos.first..t_obj_infos.last loop
12 insert into t
13 values (t_obj_infos(i).object_id, t_obj_infos(i).object_name);
14 end loop;
15 end if;
16 end P_ERRORSTACK_TEST;
17 /
Procedure created
注意三个细节：首先，我们是逐条进行数据的插入动作的，而不是insert into select方式。第二，我们在for循环里面插入数据，循环次数多，实际上不能逐条进行检查判断。第三，我们插入的是dba_objects所有的数据，必然一部分插入数据和原始数据表中存在主键冲突的情况。
实际执行中，报错。
SQL> exec P_ERRORSTACK_TEST;
begin P_ERRORSTACK_TEST; end;
ORA-00001: 违反唯一约束条件 (SCOTT.PK_T)
ORA-06512: 在 "SCOTT.P_ERRORSTACK_TEST", line 12
ORA-06512: 在 line 1
有过调试和测试经验的朋友们非常熟悉这样的情景。大批量数据测试的时候，存储过程作业包括成千上万相同或者不同的SQL，执行数据海量。执行半天之后，报错。类似的错误还有如长度（数字字符串）超过限制、将空值null插入到非空约束字段，违反外键约束等等。
这样的原因无非几种，一是程序的Bug，开发人员没有考虑到业务场景，一般出现在null值计算结果null上。其二是设计问题，业务人员和设计人员没有考虑到当前这样的异常场景。最后也是比较麻烦的，就是测试数据来源有问题，需要进行额外清理工作。
无论原因是上述哪种，有两件事情是必须做到，是哪句SQL报错，报错的数据参数值是什么。
当然，我们可以认为加入调试代码来输入执行过程，从而实现定位。我们更方便的是使用error stack的诊断事件方法来进行定位。
2、Error Stack诊断
Oracle是一个极其复杂的体系。为了便于进行调试和诊断，Oracle提供了很多的“后门”装置。我们最熟悉的10046和10053就是用来诊断SQL语句执行过程和优化器工作情况的。
Error Stack是另一种诊断事件。当启动诊断事件时候，需要设置一个错误编号ora。在启动跟踪过程中，一旦报错指定的code，Oracle就会将当前信息，如SQL和内存栈情况dump成trace文件。
和10046一样，error stack也有对应的level取值。目前我们常用的取值有4个等级。一般为了进行诊断，笔者建议将最高等级信息输出比较好。
下面我们使用error stack来解决问题。
--启动error stack监控过程
SQL> alter session set events '1 trace name errorstack level 4';
Session altered
SQL> exec P_ERRORSTACK_TEST;
begin P_ERRORSTACK_TEST; end;
ORA-00001: 违反唯一约束条件 (SCOTT.PK_T)
ORA-06512: 在 "SCOTT.P_ERRORSTACK_TEST", line 12
ORA-06512: 在 line 1
--关闭过程
SQL> alter session set events '1 trace name errorstack off';
Session altered
注意：error stack可以在system和session level进行设置。我们使用session level就可以满足大部分场景的处理要求了。
检查视图v$diag_info，来定位trace文件位置。
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/diag/rdbms/wilson/wilson/trace/wilson_ora_3813.trc
此时，我们在指定目录下面可以找到文件。
[oracle@bspdev ~]$ cd /u01/diag/rdbms/wilson/wilson/trace/
[oracle@bspdev trace]$ ls -l | grep 3813
-rw-r----- 1 oracle oinstall 7866074 Sep 4 05:20 wilson_ora_3813.trc
-rw-r----- 1 oracle oinstall 210205 Sep 4 05:20 wilson_ora_3813.trm
下面要做的就是解析trace文件。
3、Trace文件解析
应该说，使用最高level生成的诊断文件，体积是很大的。其中一些内部的信息，也是很有学习和参考意义。但是对于诊断来说，数据检索定位是非常重要的。
首先，我们在文件头，可以发现错误SQL语句的信息。Oracle显然在报错的时候，将SQL语句捕获到。
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=4, mask=0x0)
----- Error Stack Dump -----
ORA-00001: 违反唯一约束条件 (SCOTT.PK_T)
----- Current SQL Statement for this session (sql_id=ccf9mag4p402f) -----
INSERT INTO T VALUES (:B1 , :B2 )
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x4b5df860 12 procedure SCOTT.P_ERRORSTACK_TEST
0x497b9ee0 1 anonymous block
在error stack调用结构中，我们可以定位到具体是哪一个方法报错，哪一个SQL报错。Object handle表示PL/SQL程序块在内存中缓存的结构。这里，我们定位到了报错语句SQL，是sql_id= ccf9mag4p402f的insert语句。
之后，在trace文件中进行检索sql_id信息，找到了语句的执行过程Trace。
----- Dump Cursor sql_id=ccf9mag4p402f xsc=0x559a168 cur=0x700d60 -----
LibraryHandle: Address=4b5d671c Hash=c952004e LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=INSERT INTO T VALUES (:B1 , :B2 )
FullHashValue=ecb9f90364e2dbcec6393353c952004e Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3377594446 wnerIdn=84
Statistics: InvalidationCount=0 ExecutionCount=143716 LoadCount=2 ActiveLocks=1 TotalLockCount=1 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=1 HandleInUse=1
Concurrency: DependencyMutex=4b5d6784(0, 3, 0, 0) Mutex=4b5d67d0(43, 22, 0, 6)
（篇幅原因，有省略……）
kgsccflg=9 llk[0x559a16c,0x559a16c] idx=1004f
xscflg=c0110676 fl2=1d020000 fl3=422a2188 fl4=100
----- Bind Byte Code (IN) -----
Opcode = 6 Bind Rpi Scalar Sql In(may be out) Nocopy NoSkip
Offsi = 36, ffsi = 0
Opcode = 6 Bind Rpi Scalar Sql In(may be out) Nocopy NoSkip
Offsi = 36, ffsi = 20
----- Bind Info (kkscoacd) -----
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=0072e3d4 bln=22 avl=04 flg=09
value=73180
Bind#1
oacdty=01 mxl=128(128) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=873 siz=128 ff=0
kxsbbbfp=0072e404 bln=128 avl=07 flg=09
value="PK_DEPT"
Frames pfr 0x559a110 siz=2572 efr 0x559a088 siz=2532
Cursor frame. dump
enxt: 3.0x000004c4 enxt: 2.0x00000028 enxt: 1.0x000004f8
pnxt: 1.0x00000028
kxscphp=0x739424 siz=1000 inu=384 nps=224
kxscbhp=0x33e3060 siz=1000 inu=92 nps=0
Starting SQL statement dump
SQL Information
user_id=84 user_name=SCOTT module=PL/SQL Developer action=Command Window - New
sql_id=ccf9mag4p402f plan_hash_value=0 problem_type=0
注意后面标红的部分说明这个SQL有两个绑定变量，重要的是记录了绑定变量当前的取值：object_id为73180。表明SQL在插入到73180的时候，报错发生。
我们验证一下。
SQL> select object_id from t;
OBJECT_ID
----------
73179
73180
73181
73182
（篇幅原因，有省略……）
78028
20 rows selected
当前数据表中已经存在73180，主键重复必然报错。
4、结论
Error stack是一个非常方便的小工具，可以在诊断、调试程序的时候帮助解决很多问题。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-772116/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-772116/
893
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
