客户数据库出现ORA-600(19004)错误。
错误信息如下：
ORA-00600: internal error code, arguments: [19004], [], [], [], [], [], [], [] |
对应的详细TRACE为：
*** 2012-03-15 13:10:31.283 ksedmp: internal OR fatal error ORA-00600: internal error code, arguments: [19004], [], [], [], [], [], [], [] CURRENT SQL statement FOR this SESSION: SELECT DISTINCT loc.id AS col_0_0_, temp2_.id AS col_1_0_, bloc.routeIndex AS col_2_0_, loc.id AS id32_, loc.createdTime AS createdT2_32_, loc.CREATOR_ID AS CREATOR3_32_, loc.DOMAIN_ID AS DOMAIN4_32_, loc.lockVersion AS lockVers5_32_, loc.reMark AS reMark32_, loc.status_id AS status85_32_, loc.udf1 AS udf7_32_, loc.udf2 AS udf8_32_, loc.udf3 AS udf9_32_, loc.udf4 AS udf10_32_, loc.udf5 AS udf11_32_, loc.udf6 AS udf12_32_, loc.udf7 AS udf13_32_, loc.udf8 AS udf14_32_, loc.updatedTime AS updated15_32_, loc.UPDATOR_ID AS UPDATOR16_32_, loc.fixCreatedTime AS fixCrea17_32_, loc.fixCreatedTimeForDisp AS fixCrea18_32_, loc.fixExpiredTime AS fixExpi19_32_, loc.fixExpiredTimeForDisp AS fixExpi20_32_, loc.fixStatus_id AS fixStatus87_32_, loc.fixStatusForDisp AS fixStat21_32_, loc.lotAtt01 AS lotAtt22_32_, loc.lotAtt01ForDisp AS lotAtt23_32_, loc.lotAtt02 AS lotAtt24_32_, loc.lotAtt02ForDisp AS lotAtt25_32_, loc.lotAtt03 AS lotAtt26_32_, loc.lotAtt03ForDisp AS lotAtt27_32_, loc.lotAtt04 AS lotAtt28_32_, loc.lotAtt04ForDisp AS lotAtt29_32_, loc.lotAtt05 AS lotAtt30_32_, loc.lotAtt05ForDisp AS lotAtt31_32_, loc.lotAtt06 AS lotAtt32_32_, loc.lotAtt06ForDisp AS lotAtt33_32_, loc.lotAtt07 AS lotAtt34_32_, loc.lotAtt07ForDisp AS lotAtt35_32_, loc.lotAtt08 AS lotAtt36_32_, loc.lotAtt08ForDisp AS lotAtt37_32_, loc.lotAtt09 AS lotAtt38_32_, loc.lotAtt09ForDisp AS lotAtt39_32_, loc.lotAtt10 AS lotAtt40_32_, loc.lotAtt10ForDisp AS lotAtt41_32_, loc.lotAtt11 AS lotAtt42_32_, loc.lotAtt11ForDisp AS lotAtt43_32_, loc.lotAtt12 AS lotAtt44_32_, loc.lotAtt12ForDisp AS lotAtt45_32_, loc.lotHeader_id AS lotHeader89_32_, loc.asnTraceId AS asnTraceId32_, loc.cubicEach AS cubicEach32_, loc.cubicUom AS cubicUom32_, loc.customer_id AS customer86_32_, loc.grossWeightEach AS grossWe49_32_, loc.grossWeightUom AS grossWe50_32_, loc.inboundTime AS inbound51_32_, loc.location_id AS location83_32_, loc.manufacturer_id AS manufac88_32_, loc.netWeightEach AS netWeig52_32_, loc.netWeightUom AS netWeig53_32_, loc.org_id AS org82_32_, loc.packCode AS packCode32_, loc.priceEach AS priceEach32_, loc.priceUom AS priceUom32_, loc.qtyAllocatedEach AS qtyAllo57_32_, loc.qtyAllocatedUom AS qtyAllo58_32_, loc.qtyConfirmEach AS qtyConf59_32_, loc.qtyConfirmUom AS qtyConf60_32_, loc.qtyEach AS qtyEach32_, loc.qtyHold4PaEach AS qtyHold62_32_, loc.qtyHold4PaUom AS qtyHold63_32_, loc.qtyLendEach AS qtyLend64_32_, loc.qtyLendUom AS qtyLendUom32_, loc.qtyLockEach AS qtyLock66_32_, loc.qtyLockUom AS qtyLockUom32_, loc.qtyMendEach AS qtyMend68_32_, loc.qtyMendUom AS qtyMendUom32_, loc.qtyOnHoldEach AS qtyOnHo70_32_, loc.qtyOnHoldUom AS qtyOnHo71_32_, loc.qtyPickedEach AS qtyPick72_32_, loc.qtyPickedUom AS qtyPick73_32_, loc.qtyTempEach AS qtyTemp74_32_, loc.qtyTempUom AS qtyTempUom32_, loc.qtyUom AS qtyUom32_, loc.qtyUseEach AS qtyUseEach32_, loc.qtyUseUom AS qtyUseUom32_, loc.sku_id AS sku84_32_, loc.traceCode AS traceCode32_, loc.uom_id AS uom80_32_, loc.warehouse_id AS warehouse81_32_ FROM LOC loc, BLOC bloc, OD od, TEMP temp2_ WHERE loc.location_id=bloc.id AND od.id=temp2_.id AND loc.lotHeader_id=od.lotHeader_id AND (od.fixCreatedTime IS NULL OR od.fixCreatedTime=loc.fixCreatedTime) AND (od.fixExpiredTime IS NULL OR od.fixExpiredTime=loc.fixExpiredTime) AND (od.fixStatus_id IS NULL OR od.fixStatus_id=loc.fixStatus_id) AND (od.lotAtt01 IS NULL OR od.lotAtt01=loc.lotAtt01) AND (od.lotAtt02 IS NULL OR od.lotAtt02=loc.lotAtt02) AND (od.lotAtt03 IS NULL OR od.lotAtt03=loc.lotAtt03) AND (od.lotAtt04 IS NULL OR od.lotAtt04=loc.lotAtt04) AND (od.lotAtt05 IS NULL OR od.lotAtt05=loc.lotAtt05) AND (od.lotAtt06 IS NULL OR od.lotAtt06=loc.lotAtt06) AND (od.lotAtt07 IS NULL OR od.lotAtt07=loc.lotAtt07) AND (od.lotAtt08 IS NULL OR od.lotAtt08=loc.lotAtt08) AND (od.lotAtt09 IS NULL OR od.lotAtt09=loc.lotAtt09) AND (od.lotAtt10 IS NULL OR od.lotAtt10=loc.lotAtt10) AND (od.lotAtt11 IS NULL OR od.lotAtt11=loc.lotAtt11) AND (od.lotAtt12 IS NULL OR od.lotAtt12=loc.lotAtt12) AND CASE WHEN od.picLocation_id IS NULL THEN loc.location_id ELSE od.picLocation_id END=loc.location_id AND od.sku_id=loc.sku_id AND loc.uom_id=od.uom_id AND loc.warehouse_id=od.wh_id AND loc.customer_id=od.customer_id AND loc.qtyUseUom>0 ORDER BY loc.qtyUseUom DESC, bloc.routeIndex ASC, loc.id ASC ----- Call Stack Trace ----- calling CALL entry argument VALUES IN hex location TYPE point (? means dubious VALUE) -------------------- -------- -------------------- ---------------------------- ksedst()+31 CALL ksedst1() 000000000 ? 000000001 ? 7FBFFF0300 ? 7FBFFF0360 ? 7FBFFF02A0 ? 000000000 ? ksedmp()+610 CALL ksedst() 000000000 ? 000000001 ? 7FBFFF0300 ? 7FBFFF0360 ? 7FBFFF02A0 ? 000000000 ? ksfdmp()+21 CALL ksedmp() 000000003 ? 000000001 ? 7FBFFF0300 ? 7FBFFF0360 ? 7FBFFF02A0 ? 000000000 ? kgeriv()+176 CALL ksfdmp() 000000003 ? 000000001 ? 7FBFFF0300 ? 7FBFFF0360 ? 7FBFFF02A0 ? 000000000 ? kgesiv()+119 CALL kgeriv() 0066876E0 ? 00673CEE0 ? 000000000 ? 000000000 ? 7FBFFF02A0 ? 000000000 ? ksesic0()+209 CALL kgesiv() 0066876E0 ? 00673CEE0 ? 000004A3C ? 000000000 ? 7FBFFF1080 ? 000000000 ? kkejeq()+5152 CALL ksesic0() 000004A3C ? 2A97403DB0 ? 0000000CA ? 2A97403DB0 ? 000000002 ? 000000000 ? kkeEqJoinSel()+2602 CALL kkejeq() 2A973C2930 ? 2A97403DB0 ? 000000000 ? 2A97403DB0 ? 000000002 ? 000000000 ? kkepsl()+10561 CALL kkeEqJoinSel() 7FBFFF1BB0 ? 7FBFFF1BD0 ? 7FBFFF1BC0 ? 7FBFFF1BE0 ? 2A971E68F8 ? 2A00000002 ? kkeasl()+2125 CALL kkepsl() 2A971E68F8 ? 7FBFFF2438 ? 000000002 ? 2A971EFB30 ? 2A971E68F8 ? 2A00000002 ? kkeosl()+1447 CALL kkeasl() 2A971E6970 ? 7FBFFF3DB8 ? 000000002 ? 000000000 ? 000000000 ? 2A971EFB30 ? kkejcd()+1466 CALL kkeosl() 2A971E6BF0 ? 7FBFFF4010 ? 000000002 ? 000000000 ? 000000000 ? 2A971EFB30 ? kkojnp()+3920 CALL kkejcd() 2A973CF670 ? 2A973D3628 ? 2A974407E8 ? 000000016 ? 2A973D5008 ? 000000003 ? kkocnp()+166 CALL kkojnp() 2A973D5008 ? 000000000 ? 2A974407E8 ? 000000016 ? 2A973D5008 ? 000000000 ? kkooqb()+2258 CALL kkocnp() 2A973D5008 ? 000000001 ? 2A974407E8 ? 000000016 ? 2A973D5008 ? 000000000 ? kkoqbc()+2083 CALL kkooqb() 000000000 ? 000000001 ? 0000007D0 ? 000000004 ? 2A00000000 ? 2A00000006 ? apakkoqb()+170 CALL kkoqbc() 7FBFFF7D80 ? 2A97393428 ? 0000007D0 ? 000000001 ? 2A00000000 ? 000000000 ? apaqbdDescendents() CALL apakkoqb() 7FBFFF7D80 ? 2A97393428 ? +399 1D23E4740 ? 000000001 ? 2A00000000 ? 000000000 ? apaqbdListReverse() CALL apaqbdDescendents() 7FBFFF7D80 ? 2A97393428 ? +68 1D23E4740 ? 000000001 ? 2A00000000 ? 000000000 ? apadrv()+586 CALL apaqbdListReverse() 7FBFFF7D80 ? 2A97393428 ? 1D23E4740 ? 000000001 ? 2A00000000 ? 000000000 ? opitca()+1512 CALL apadrv() 1D23E4740 ? 2A97393428 ? 1D23E4740 ? 000000001 ? 2A00000000 ? 000000000 ? kksFullTypeCheck()+ CALL opitca() 2A972335A8 ? 1D23E4740 ? 30 1D23E4740 ? 000000001 ? 2A00000000 ? 000000000 ? rpiswu2()+420 CALL kksFullTypeCheck() 7FBFFF9328 ? 1D23E4740 ? 1D23E4740 ? 000000001 ? 2A00000000 ? 000000000 ? kksLoadChild()+9500 CALL rpiswu2() 1F736B950 ? 00000003A ? 1DFC84F24 ? 000000009 ? 1DFAD88E0 ? 10000003A ? kxsGetRuntimeLock() CALL kksLoadChild() 0066876E0 ? 1FAB8C5C0 ? +1353 7FBFFFA7C0 ? 000000000 ? 1DFC84DC0 ? 2A972335A8 ? kksfbc()+15084 CALL kxsGetRuntimeLock() 0066876E0 ? 2A972335A8 ? 7FBFFFA7C0 ? 000000000 ? 1DFC84DC0 ? 2A972335A8 ? kkspsc0()+1548 CALL kksfbc() 2A972335A8 ? 000000003 ? 000000108 ? 2A97203D18 ? 0000016A6 ? 000000000 ? kksParseCursor()+14 CALL kkspsc0() 2A97211788 ? 2A97203D18 ? 2 0000016A6 ? 000000003 ? E77100A400000006 ? 1E77100A4 ? opiosq0()+1641 CALL kksParseCursor() 7FBFFFB208 ? 2A97203D18 ? 0000016A6 ? 000000003 ? E77100A400000006 ? 1E77100A4 ? kpooprx()+315 CALL opiosq0() 000000003 ? 00000000E ? 7FBFFFB3F8 ? 0000000A4 ? E77100A400000006 ? 1E77100A4 ? kpoal8()+799 CALL kpooprx() 7FBFFFE5A4 ? 2A97203D18 ? 0000016A5 ? 000000001 ? 000000000 ? 1E77100A4 ? opiodr()+984 CALL kpoal8() 00000005E ? 000000017 ? 7FBFFFE5A0 ? 000000001 ? 000000001 ? 1E77100A4 ? ttcpip()+1012 CALL opiodr() 00000005E ? 000000017 ? 7FBFFFE5A0 ? 000000000 ? 0059B1310 ? 1E77100A4 ? opitsk()+1322 CALL ttcpip() 00668F3B0 ? 000000003 ? 7FBFFFE5A0 ? 000000000 ? 7FBFFFE098 ? 7FBFFFE708 ? opiino()+1026 CALL opitsk() 000000003 ? 000000000 ? 7FBFFFE5A0 ? 000000001 ? 000000000 ? 67502D200000001 ? opiodr()+984 CALL opiino() 00000003C ? 000000004 ? 7FBFFFF768 ? 000000000 ? 000000000 ? 67502D200000001 ? opidrv()+547 CALL opiodr() 00000003C ? 000000004 ? 7FBFFFF768 ? 000000000 ? 0059B0DC0 ? 67502D200000001 ? sou2o()+114 CALL opidrv() 00000003C ? 000000004 ? 7FBFFFF768 ? 000000000 ? 0059B0DC0 ? 67502D200000001 ? opimai_real()+163 CALL sou2o() 7FBFFFF740 ? 00000003C ? 000000004 ? 7FBFFFF768 ? 0059B0DC0 ? 67502D200000001 ? main()+116 CALL opimai_real() 000000002 ? 7FBFFFF7D0 ? 000000004 ? 7FBFFFF768 ? 0059B0DC0 ? 67502D200000001 ? __libc_start_main() CALL main() 000000002 ? 7FBFFFF7D0 ? +219 000000004 ? 7FBFFFF768 ? 0059B0DC0 ? 67502D200000001 ? _start()+42 CALL __libc_start_main() 000713988 ? 000000002 ? 7FBFFFF918 ? 005288D70 ? 000000000 ? 000000002 ? --------------------- Binary Stack Dump --------------------- |
这个SQL语句很长，查询了大量的列，且使用了DISTINCT，而且关联条件显得十分复杂，所以出现ORA-600错误也不奇怪。
检查MOS发现，问题和Bug 9022470 – Query optimization fails with OERI[19004] [ID 9022470.8]比较类似，除了当前的10204版本符合这个bug影响的版本外，堆栈信息错误完全符合这个bug的报错信息，而且在这个查询中，其中一个列确实在连接查询中出现了多次，这也同样符合bug的描述。
这个错误影响的版本包括10.2.0.4、11.1.0.7和11.2.0.1，Oracle在11.2.0.2中修复了这个bug。
对于这个错误，如果无法升级解决，那么只能通过修改SQL语法，或者尝试降低OPTIMIER_FEATURES_ENABLE的值来解决。