sql – 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:\APP\ORACLEUSER\diag\rdbms\orcl\orcl\trace\orcl_ora_2084.trc'>
 <txt>Errors in file C:\APP\ORACLEUSER\diag\rdbms\orcl\orcl\trace\orcl_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:\APP\ORACLEUSER\diag\rdbms\orcl\orcl\trace\orcl_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,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:\app\oracleuser\diag\rdbms\orcl\orcl\trace\orcl_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中测试为固定和工作(没有解决方法)

sql – ORA 7445：导致Oracle 12c崩溃的查询

解决方法

猜你在找的MsSQL相关文章