这个错误是由于SQL中包含ORDER SIBLINGS BY DESC语句引起的。
看一个简单的例子:
SQL> create table t (id number, parent_id number, name varchar2(30));
Table created.
SQL> insert into t values (1, null, 'a');
1 row created.
SQL> insert into t values (2, 1, 'b');
1 row created.
SQL> insert into t values (3, 1, 'c');
1 row created.
SQL> insert into t values (4, 2, 'd');
1 row created.
SQL> commit;
Commit complete.
SQL> select id, parent_id, name
2 from t
3 start with id = 1
4 connect by prior id = parent_id
5 ;
ID PARENT_ID NAME
---------- ---------- ------------------------------
1 a
2 1 b
4 2 d
3 1 c
SQL> select id, parent_id, name
2 from t
3 start with id = 1
4 connect by prior id = parent_id
5 order by name;
ID PARENT_ID NAME
---------- ---------- ------------------------------
1 a
2 1 b
3 1 c
4 2 d
SQL> select id, parent_id, name
2 from t
3 start with id = 1
4 connect by prior id = parent_id
5 order siblings by name;
ID PARENT_ID NAME
---------- ---------- ------------------------------
1 a
2 1 b
4 2 d
3 1 c
SQL> select id, parent_id, name
2 from t
3 start with id = 1
4 connect by prior id = parent_id
5 order siblings by name desc;
ID PARENT_ID NAME
---------- ---------- ------------------------------
1 a
3 1 c
2 1 b
4 2 d
对于普通的列执行ORDER SIBLINGS BY DESC操作不会引发错误,而如果这个列是通过SYS_CONNECT_BY_PATH函数获取的,就会导致ORA-600错误:
SQL> select id, parent_id, sys_connect_by_path(name, '/') name
2 from t
3 start with id = 1
4 connect by prior id = parent_id
5 order by name;
ID PARENT_ID NAME
---------- ---------- ------------------------------
1 /a
2 1 /a/b
4 2 /a/b/d
3 1 /a/c
SQL> select id, parent_id, sys_connect_by_path(name, '/') name
2 from t
3 start with id = 1
4 connect by prior id = parent_id
5 order siblings by name;
ID PARENT_ID NAME
---------- ---------- ------------------------------
1 /a
2 1 /a/b
4 2 /a/b/d
3 1 /a/c
SQL> select id, parent_id, sys_connect_by_path(name, '/') name
2 from t
3 start with id = 1
4 connect by prior id = parent_id
5 order siblings by name desc;
from t
*
ERROR at line 2:
ORA-00600: internal error code, arguments: [evapth : unexpected evaluation], [], [], [], [], [], [], []
对应的alert文件信息为:
Errors in file /opt/ora10g/admin/test08/udump/test08_ora_14719.trc:
ORA-00600: internal error code, arguments: [evapth : unexpected evaluation], [], [], [], [], [], [], []
而TRACE文件开头部分为:
[oracle@yans1 bdump]$ more /opt/ora10g/admin/test08/udump/test08_ora_14719.trc
/opt/ora10g/admin/test08/udump/test08_ora_14719.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/ora10g/product/10.2.0/db_1
System name: Linux
Node name: yans1
Release: 2.6.9-42.0.0.0.1.ELsmp
Version: #1 SMP Sun Oct 15 15:13:57 PDT 2006
Machine: x86_64
Instance name: test08
Redo thread mounted by this instance: 1
Oracle process number: 42
Unix process pid: 14719, image: oracle@yans1 (TNS V1-V3)
*** ACTION NAME:() 2009-08-21 16:52:51.328
*** MODULE NAME:(SQL*Plus) 2009-08-21 16:52:51.328
*** SERVICE NAME:(SYS$USERS) 2009-08-21 16:52:51.328
*** SESSION ID:(128.11765) 2009-08-21 16:52:51.328
*** 2009-08-21 16:52:51.328
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [evapth : unexpected evaluation], [], [], [], [], [], [], []
Current SQL statement for this session:
select id, parent_id, sys_connect_by_path(name, '/') name
from t
start with id = 1
connect by prior id = parent_id
order siblings by name desc
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+31 call ksedst1() 000000000 ? 000000001 ?
7FBFFF8BB0 ? 7FBFFF8C10 ?
7FBFFF8B50 ? 000000000 ?
ksedmp()+610 call ksedst() 000000000 ? 000000001 ?
7FBFFF8BB0 ? 7FBFFF8C10 ?
7FBFFF8B50 ? 000000000 ?
ksfdmp()+21 call ksedmp() 000000003 ? 000000001 ?
7FBFFF8BB0 ? 7FBFFF8C10 ?
7FBFFF8B50 ? 000000000 ?
kgerinv()+161 call ksfdmp() 000000003 ? 000000001 ?
7FBFFF8BB0 ? 7FBFFF8C10 ?
7FBFFF8B50 ? 000000000 ?
kgeasnmierr()+163 call kgerinv() 006618E20 ? 2A972D1168 ?
7FBFFF8C10 ? 7FBFFF8B50 ?
000000000 ? 000000000 ?
evapth()+258 call kgeasnmierr() 006618E20 ? 2A972D1168 ?
7FBFFF8C10 ? 7FBFFF8B50 ?
000210002 ? 001832BC8 ?
evaopn2()+415 call evapth() 006618E20 ? 2A972D1168 ?
7FBFFF8C10 ? 7FBFFF8B50 ?
000210002 ? 001832BC8 ?
evacom()+332 call evaopn2() 7FBFFF8C10 ? 2A972FC688 ?
0FD3D3950 ? 7FBFFF8B50 ?
2A973748D8 ? 001832BC8 ?
qercbiRowP()+3549 call evacom() 0FD3D39D0 ? 2A972FC688 ?
0FD3D3950 ? 7FBFFF8B50 ?
2A973748D8 ? 001832BC8 ?
kdstf1100101km()+63 call qercbiRowP() 000000003 ? 000000001 ?
7 2A972FC688 ? 000000003 ?
2A972FC3B8 ? 2A972FC478 ?
kdsttgr()+24760 call kdstf1100101km() 093324078 ? 000000000 ?
002F3E648 ? 0FD3D3FB8 ?
000000001 ? 000000000 ?
qertbFetch()+645 call kdsttgr() 2A9731B540 ? 000000000 ?
000000001 ? 2A9731B4A8 ?
000000001 ? 002F3E648 ?
在metalink中以evapth : unexpected evaluation为参数查询ORA-600错误,发现Oracle在文档Doc ID: 284511.1中描述了多个和这个参数相关的bug。虽然没有一个bug的描述和当前错误完全一致,但是所有的这些bug都和树形查询有关,且大部分的bug都与SYS_CONNECT_BY_PATH函数有关。
这个功能很难绕过去,好在使用的概率并不大,否则就只能等Oracle提供补丁了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-612905/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-612905/