今天客户的oracle 11.2.0.2数据库碰到了ora-600这个错误。详细错误信息如下：fri sep 16 15:23:52 2011errors in file /u01/diag
今天客户的oracle 11.2.0.2数据库碰到了ora-600这个错误。
详细错误信息如下：
fri sep 16 15:23:52 2011
errors in file /u01/diag/rdbms/ora1/ora1/trace/ora1_ora_20382140.trc (incident=169704):
ora-00600: 内部错误代码, 参数: [kkoipt:invalid join method], [1], [0], [], [], [], [], [], [], [], [], []
incident details in: /u01/diag/rdbms/ora1/ora1/incident/incdir_169704/ora1_ora_20382140_i169704.trc
fri sep 16 15:24:00 2011
dumping diagnostic data in directory=[cdmp_20110916152400], requested by (instance=1, osid=20382140), summary=[incident=169704].
use adrci or support workbench to package the incident.
see note 411.1 at my oracle support for error and packaging details.
而对应的详细trace如下：
bash-3.2$ more /u01/diag/rdbms/ora1/ora1/incident/incdir_169704/ora1_ora_20382140_i169704.trc
dump file /u01/diag/rdbms/ora1/ora11/incident/incdir_169704/ora1_ora_20382140_i169704.trc
oracle database 11g enterprise edition release 11.2.0.2.0 - 64bit production
with the partitioning, real application clusters, automatic storage management, olap,
data mining and real application testing options
oracle_home = /u01/product/11.2.0/dbhome_1
system name: aix
node name: node1
release: 1
version: 6
machine: 00f6cd264c00
instance name: ora1
redo thread mounted by this instance: 1
oracle process number: 193
unix process pid: 20382140, image: oracle@s180
*** 2011-09-16 15:23:52.275
*** session id:(29.7169) 2011-09-16 15:23:52.275
*** client id:() 2011-09-16 15:23:52.275
*** service name:(ora1) 2011-09-16 15:23:52.275
*** module name:(toad 10.5.0.41) 2011-09-16 15:23:52.275
*** action name:() 2011-09-16 15:23:52.275
dump continued from file: /u01/diag/rdbms/ora1/ora1/trace/ora1_ora_20382140.trc
ora-00600: 内部错误代码, 参数: [kkoipt:invalid join method], [1], [0], [], [], [], [], [], [], [], [], []
========= dump for incident 169704 (ora 600 [kkoipt:invalid join method]) ========
*** 2011-09-16 15:23:52.336
dbkeddefdump(): starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- current sql statement for this session (sql_id=7ukzmn3p6zby6) -----
select *
from (
select a.childpolicyno, a.policyno, e.exportno corpno, a.effectdate,
to_char (a.lapsedate, 'yyyy-mm-dd') lapsedate, e.chnname,
e.address, e.keyflag, e.specialflag, g.empname,
g.secdeptname, f.type, rownum as id
from t_schildpolicy a,
t_spolicy f,
t_exportcorp e,
t_nodeinfo d,
v_employeecustomer g
where f.policyno = a.policyno
and f.insurantno = e.exportno
and f.policyno = g.productid(+)
and d.nodeid = f.nodeid
and e.chnname like '%%'
and ((d.corpid = '3502'))
and rownum order by a.policyno)
where id between 1 and 10
----- call stack trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+40 bl 107b6e01c ffffffffffecca8 ? 000002004 ?
000000001 ? 000000003 ?
000000000 ? 000000002 ?
000000001 ? 000000000 ?
ksedst1()+104 call skdstdst() ffffffffffebcb0 ? 000002004 ?
110a597a0 ? 10a027b2c ?
110a597a0 ? 000000000 ?
ffffffffffebde0 ? 700000007 ?
ksedst()+40 call ksedst1() 3030000000000 ? 002050033 ?
10a027b20 ? 700000000025c ?
000000000 ? 000000000 ?
10a027180 ? 000000000 ?
dbkeddefdump()+2828 call ksedst() ffffffffffebe90 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 300000003 ?
ksedmp()+76 call dbkeddefdump() 310a597a0 ? 1100010c8 ?
ffffffffffec490 ?
28444040fffec66c ?
100148568 ? 1096635a8 ?
ffffffffffec4e0 ? 11064b598 ?
ksfdmp()+88 call ksedmp() 000000000 ? 000000000 ?
0096635c3 ? 109cb2c50 ?
200000000000000 ? 000000000 ?
110c221e8 ? 110a597a0 ?
dbgexphaseii()+1212 call ksfdmp() 000002004 ? 110a597a0 ?
000000000 ? ffffffffffec658 ?
ffffffffffec580 ?
ffffffffffecca8 ? 1001d04b8 ?
110c221e8 ?
dbgexprocesserror() call dbgexphaseii() 110a597a0 ? 110c203f8 ?
+3604 0000296e8 ? 200000000 ?
ffffffffffed258 ? 00000006a ?
000000000 ? 000000000 ?
dbgeexecuteforerror call dbgexprocesserror() 110a597a0 ? 110c221e8 ?
()+72 100000000 ? 000000000 ?
110d01c88 ? 000000000 ?
110cadc78 ? 110c23f30 ?
dbgeposterrorkge()+ call dbgeexecuteforerror fffffffffff0830 ?
1152 () b7417335409b9b1b ?
fffffffffff06f0 ? 0409b9800 ?
10524ea10 ?
2147ae154168e65f ?
10524ea10 ? 000000000 ?
dbkepostkge_kgsf()+ call dbgeposterrorkge() 002050000 ? 001160000 ?
64 25810001330 ? 000000000 ?
110001330 ? fffffffffff1510 ?
109613cd0 ? 110cb3f18 ?
这个sql错误的奇特之处在于，如果将order by语句中的a.policyno变成其他列，则不会导致错误，甚至是换成与之相等关联的f.policyno，同样不会报错。
这显然是一个bug，查询metalink发现，，居然目前唯一确认影响的版本就是11.2.0.2，这个bug的描述为bug 12591252 query with order by fails with ora-600 [kkoipt:invalid join method]。而oracle的解释是，优化器试图通过索引来避免排序操作时，可能引发这个bug。这正好说明了为什么sql中换成其他的列就不会导致错误。
目前这个bug在windows平台的11.2.0.2的patch 10中被fixed，其他平台只能通过单独的12591252补丁来解决这个问题。当然如果能通过改写sql来实现这个目的，无疑代价是最小的。