這個錯誤是由於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提供補丁了。

ORA-600(evapth : unexpected evaluation)錯誤

相關文章