ORA 7445：导致Oracle 12c崩溃的查询
在Oracle 11上所有测试都通过OK后，我正在Oracle 12上测试我的应用程序。在测试期间，应用程序在运行某个查询时一直失去与Oracle的连接。 这是一个简单的例子，它精确地复制了这个问题：
--create an example table
CREATE TABLE ERROR_TABLE(
PKID INT,
STRING_COLUMN VARCHAR2(255 CHAR)
);
--and run the offending query
SELECT
T.*,
ROW_NUMBER() OVER(
ORDER BY TO_NUMBER(REGEXP_SUBSTR(STRING_COLUMN,'^[0-9]*[.]*[0-9]*')) ASC,
STRING_COLUMN ASC,
PKID ASC
)
FROM
(
SELECT
PKID,
(SELECT MIN(STRING_COLUMN) FROM ERROR_TABLE T1 WHERE T1.PKID = T2.PKID) AS STRING_COLUMN
FROM ERROR_TABLE T2
)T;
当我运行这个查询时，与Oracle的连接被删除。 Oracle警报日志中记录了一个“事件”，但没有说明它发生的原因。 它似乎是TO_NUMBER（...）和SELECT（MIN ...部分）的组合，如果我替换其中的任何一个，问题就会消失。但是，由于经过测试和验证，我不愿意这样做，并且该技术出现在整个程序的多个地方。
有没有其他人遇到过这样的事情？ 是什么造成的？
我正在运行Oracle 12.1.0.1.0。 服务器是64位的，客户端是32位的。
UPDATE
以下是警报日志的相关摘录：
<msg time='2014-09-29T14:57:30.310+01:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='ADMIN-PC'
host_addr='fe80::5183:eb5:fdd6:8fce%10' module='SQL Developer' pid='2084'>
<txt>Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x0] [PC:0x7FEF691FB5A, qcsogolz()+208]
</txt>
</msg>
<msg time='2014-09-29T14:57:30.435+01:00' org_id='oracle' comp_id='rdbms'
msg_id='1305664044' type='INCIDENT_ERROR' group='Access Violation'
level='1' host_id='ADMIN-PC' host_addr='fe80::5183:eb5:fdd6:8fce%10'
prob_key='ORA 7445 [qcsogolz]' errid='40818' detail_path='C:APPORACLEUSERdiagrdbmsorclorcltraceorcl_ora_2084.trc'>
<txt>Errors in file C:APPORACLEUSERdiagrdbmsorclorcltraceorcl_ora_2084.trc (incident=40818):
ORA-07445: exception encountered: core dump [qcsogolz()+208] [ACCESS_VIOLATION] [ADDR:0x0] [PC:0x7FEF691FB5A] [UNABLE_TO_READ] []
</txt>
</msg>
<msg time='2014-09-29T14:57:30.451+01:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='ADMIN-PC'
host_addr='fe80::5183:eb5:fdd6:8fce%10' module='SQL Developer' pid='2084'>
<txt>Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
</txt>
</msg>
<msg time='2014-09-29T14:57:32.760+01:00' org_id='oracle' comp_id='rdbms'
msg_id='dbgripsto_sweep_staged_obj:15783:70631439' type='ERROR' group='ami_comp'
level='8' host_id='ADMIN-PC' host_addr='fe80::5183:eb5:fdd6:8fce%10'>
<txt>Sweep [inc][40818]: completed
</txt>
</msg>
这里是结果跟踪文件的副本：
Trace file C:APPORACLEUSERdiagrdbmsorclorcltraceorcl_ora_2084.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU : 2 - type 8664, 2 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:882M/2038M, Ph+PgF:1864M/4076M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 50
Windows thread id: 2084, image: ORACLE.EXE (SHAD)
*** 2014-09-29 14:57:30.310
*** SESSION ID:(18.63977) 2014-09-29 14:57:30.310
*** CLIENT ID:() 2014-09-29 14:57:30.310
*** SERVICE NAME:(pdborcl) 2014-09-29 14:57:30.310
*** MODULE NAME:(SQL Developer) 2014-09-29 14:57:30.310
*** ACTION NAME:() 2014-09-29 14:57:30.310
*** CONTAINER ID:(3) 2014-09-29 14:57:30.310
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x0] [PC:0x7FEF691FB5A, qcsogolz()+208]
DDE: Problem Key 'ORA 7445 [qcsogolz]' was flood controlled (0x4) (incident: 40818)
ORA-07445: exception encountered: core dump [qcsogolz()+208] [ACCESS_VIOLATION] [ADDR:0x0] [PC:0x7FEF691FB5A] [UNABLE_TO_READ] []
Dump file c:apporacleuserdiagrdbmsorclorcltraceorcl_ora_2084.trc
Mon Sep 29 14:57:30 2014
ORACLE V12.1.0.1.0 - 64bit Production vsnsta=0
vsnsql=16 vsnxtr=3
Dumping diagnostics for abrupt exit from ksedmp
ksedmp exception at address PC:0x0
ksedmp exception at 0000000000000000
Dumping initial exception call-stack
------------------- Call Stack Trace ---------------------
Frameptr RetAddr Param#1 Param#2 Param#3 Param#4 Function Name
000007FEF691FB5A 0000000000000000 0000000000630072 0000000000000000 0000000000000000 0000000000000000 qcsogolz()+208
000007FEF692BD90 000007FEF691FA8A 000000001969AE00 000007FE00000000 0000000000035490 000007FE00000000 qctcopn()+1904
000007FEF692B7B8 000007FEF692B620 0000000019696BC0 0000000022C90F80 000000014A2062A0 000000014657990E qctcopn()+408
000007FEF692B7B8 000007FEF692B620 000000014A2062A0 00000000196954C0 00000000196954C0 000007FF1A0EC938 qctcopn()+408
000007FEF692B7B8 000007FEF692B620 00000000196964E0 000007FEF65DBEE3 000000001522B400 0000000022C90F80 qctcopn()+408
000007FEF692B7B8 000007FEF692B620 0000000000000000 000000001522A7A8 000000001522A2E8 0000000000000000 qctcopn()+408
000007FEF6928BA3 000007FEF692B620 0000000022C91540 000007FEF65DBC6D 0000000000000000 000000001522A9B8 qctcpqb()+291
000007FEF6928A53 000007FEF6928A80 0000000019695C20 0000000022C90F80 0000000000035490 00000001433D61D6 qctcpqbl()+51
000000014561599A 000007FEF6928A20 00000001460088E0 000000002DDB5570 0000000000000001 000000001522A9B8 xtydrv()+138
0000000145FE901F 0000000145615910 0000000015227AC8 0000000000000000 0000000022C91540 000007FEF65DBFCC kkqcttcalo()+383
0000000145FF4C5D 0000000145FE8EA0 0000000000000000 0000000000000000 0000000000008000 0000000002000000 kkqctdrvCVM()+1501
00000001462F36EF 0000000145FF4A37 0000000000000000 0000000000000433 0000000022C91540 000007FEF61F6186 kkqvmTrMrg()+3087
00000001462F2148 00000001462F2AE0 0000000000000000 00000001422DEED1 0000000000000001 0000000000037038 kkqvmdrv2()+872
00000001460059B9 00000001462F1DE0 0000000000000000 000007FEF65E1153 0000000000000000 00000001460D7EAE kkqctdrvTD()+809
0000000145F213E1 0000000146005690 0000000000000000 000000002DDB5ED0 0000000000000002 00000000000000A1 kkqdrv()+6977
000000014600504C 0000000145F1F8A0 0000000015222578 000007FF00000000 000000000003AF58 000007FF1A0ECB80 kkqctdrvIT()+828
0000000145EE8B5A 0000000146004D10 0000000000000000 000000001522FF48 0000000000000038 000000001522FF48 apadrv()+4010
0000000143250A15 0000000145EE7BB0 0000000000000000 0000000000000433 0000000000000000 0000000000000000 opitca()+2565
000000014025C3A6 0000000143250010 00000000196EEB60 000007FF1A0ECB80 000000002DDB8CE8 0000000100000002 kksLoadChild()+8886
0000000140DA2D9E 000000014025A0F0 0000000022C91540 000007FF1839B920 000007FF1CE6F730 000007FF1839B920 kxsGetRuntimeLock()+2414
0000000140BE5A2A 0000000140DA2430 0000000022C91540 00000000196EEB60 000000002DDB8110 000000000000012C kksfbc()+15626
0000000140BDCB6E 0000000140BE1FD0 00000000196EEB60 0000000000000003 000007FF00000108 000000002DDBB020 kkspsc0()+2526
0000000140BDE812 0000000140BDC43E 0000000015242330 000000002DDBB020 0000000000000137 0000000000000003 kksParseCursor()+130
000000014630AC0C 0000000140BDE790 00000000151C6AF8 00000000151C9B98 0000000000000000 000007FEF7C21726 opiosq0()+3004
00000001442E760A 000000014630A050 000007FF00000003 000000014915B0C0 0000000000000000 00000000000000A4 kpooprx()+410
00000001442E3A52 00000001442E7470 0000000022C962AC 0000000000000001 0000000022C962D0 0000000000000001 kpoal8()+994
0000000140E685C1 00000001442E3670 0000000000000001 000000002DDBB2BC 00000000169711A0 000000002DDBB295 opiodr()+1601
000007FEF75CD7A7 0000000140E67F80 000007FF0000005E 000007FF0000001F 000000002DDBD8A0 000007FE00000000 ttcpip()+1223
0000000140E64E10 000007FEF75CD2E0 0000000022CB1140 0000000000000000 0000000000000000 0000000000000000 opitsk()+2160
0000000144768CD7 0000000140E645A0 0000000000000000 0000000000000000 000000002DDBF080 000000002DDBE7F0 opiino()+1079
0000000140E685C1 00000001447688A0 000000000000003C 0000000000000000 000000002DDBF2F0 0000000000000000 opiodr()+1601
000000013FE2F68A 0000000140E67F80 000000000000003C 0000000000000004 000000002DDBF2F0 0000000000000000 opidrv()+842
000000013FE3074E 000000013FE2F3DC 000000010000003C 000007FE00000004 000000002DDBF2F0 0000000015106A28 sou2o()+94
000000013FD612E4 000000013FE306F0 01CFDBED484830FD 0000000014A364D0 000E001D000907DE 0001015900120039 opimai_real()+276
000000013FD610BA 000000013FD611D0 0000000000000000 0000D6736DC2EA78 0000000014A364D0 000000002DDBF4A8 opimai()+170
000000013FD62239 000000013FD61010 0000000000000000 0000000149041F90 0000000000000050 000000000000196C OracleThreadStart()+713
0000000076CC59ED 000000013FD61F70 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000076CC59ED
0000000076EFC541 0000000076CC59E0 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000076EFC541
---------------- End of Call Stack Trace -----------------
这是12.1.0.1中的一个错误（错误17633803）。 它将在12.2中修复并在12.1.0.2中修补。
有一个解决方法是运行
ALTER SESSION SET "_optimizer_unnest_scalar_sq"=false;
我试着运行上面的示例代码，解决方法确实解决了12.1.0.1中的问题
UPDATE
在12.1.0.2中测试为固定和工作（没有解决方法）
链接地址: http://www.djcxy.com/p/81725.html