选择使用ORA-03113的“视图查询”崩溃:通信通道上的文件结束。

Question

我在11g企业版服务器(Oracle数据库11g企业版发布版11.2.0.1.0 -64位产品)中安装了在11g XE上开发的应用程序，主SELECT查询触发器如下：

ORA-03113: end-of-file on communication channel 
Identificador de Proceso: 14040
Identificador de Sesión: 55 Número de Serie: 22227

我简化了实际的查询，并且仍然可以用这样简单的内容再现崩溃：

SELECT table_1.table_1_id
FROM table_1
LEFT JOIN view_1  ON table_1.table_1_id=view_1.table_1_id
LEFT JOIN view_2  ON view_1.table_2_id=view_2.table_2_id
LEFT JOIN table_2 ON table_1.table_1_id=table_2.table_1_id
    AND view_1.table_2_id=table_2.table_2_id
LEFT JOIN view_3  ON table_2.table_2_id=view_3.table_2_id;

..。一旦我从查询中删除任何视图，它就会工作。到目前为止，涉及的桌子是空的。

DBA在警报日志中发现了这一点：

.#en0#=‘#en0#5’>07445: se ha encontradoón: volcado de memoria kkqtutlSetViewCols()+250 地址:0x8 不可能_至_朗读 事件详情见：

"audi_ora_5512_i39166.trc“包含以下内容(为简洁而编辑的实际查询)：

========= Dump for incident 40848 (ORA 7445 [kkqtutlSetViewCols()+250]) ========
----- Beginning of Customized Incident Dump(s) -----
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x8] [PC:0x3DC175C, kkqtutlSetViewCols()+250]

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
Process Id: 0x00000620  Thread Id : 0x00001a08    Time : Mon Jul 11 11:46:08 
Excp. Code: 0xc0000005  Excp. Type: ACCESS_VIO    Flags: 0x00000000

------------------- Registers ----------------------------
ip=0000000003DC175C sp=000000001A3A4DB0 rp=0000000044038058
r1=0000000000000000 r2=0000000000000004 r3=0000000038450640 
r4=0000000000000000 r5=000000001A3A4DB0 r6=0000000044038058 r7=0000000044033140 
r8=0000000000000000 r9=0000000000000004 r10=0000000000000004 r11=0000000000000003 
r12=0000000000000001 r13=0000000000020B78 r14=0000000044037B18 r15=0000000038450080 
------------------- End of Registers ---------------------


*** 2016-07-11 11:46:08.499
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=3dt50z5hh7gg9) -----
SELECT table_1.table_1_id
FROM table_1
LEFT JOIN view_1  ON table_1.table_1_id=view_1.table_1_id
LEFT JOIN view_2  ON view_1.table_2_id=view_2.table_2_id
LEFT JOIN table_2 ON table_1.table_1_id=table_2.table_1_id
    AND view_1.table_2_id=table_2.table_2_id
LEFT JOIN view_3  ON table_2.table_2_id=view_3.table_2_id;

----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
kkqtutlSetViewCols(           0000000000000000     49204E4F20534154
)+250                                              49434154524F504D
                                                   524F504D492E4E4F
                                                   495F4E4F49434154
kokqpoqb()+1319      CALL???  kkqtutlSetViewCols(  04400C070 044078D40 044021FE0
                              )+69                 000020B78
kokqpqb()+196        CALL???  kokqpoqb()           0440229B0 0031412FD 000001F68
                                                   000000000
kokqpqb()+318        CALL???  kokqpqb()            7FF365B8188 000010400
                                                   000000000 004FF4D30
kokqpqb()+64         CALL???  kokqpqb()            7FF365B7F48 000000000
                                                   000000000 000000000
kokqpqb()+318        CALL???  kokqpqb()            000000000 038450640 000000000
                                                   000000000
kokqpqb()+318        CALL???  kokqpqb()            000000000 000000000 000000000
                                                   000000000
kokqpqb()+64         CALL???  kokqpqb()            038450080 038450080 01A3A52E0
                                                   0440229B0
kokqpqb()+64         CALL???  kokqpqb()            000020B78 038450080 01A3A52E0
                                                   044078CB0
kokqpqb()+318        CALL???  kokqpqb()            044061FD0 0059F3106 000000000
                                                   0078E8984
kokqpqb()+318        CALL???  kokqpqb()            000000000 0078E8950 044065D78
                                                   000000000
kokqpqbc2()+166      CALL???  kokqpqb()            044065D78 0059F3025 000000000
                                                   0000221E0
kokqpqbc_i()+99      CALL???  kokqpqbc2()          000020B78 004C956C4 000000000
                                                   004C9559B
kokqpqbc()+17        CALL???  kokqpqbc_i()         0000221E0 000020B78 000020B78
                                                   038450080
kkqcttcalo()+1017    CALL???  kokqpqbc()           01A40EB60 009263551 000000002
                                                   000000001
kkqctdrvJPPD()+1176  CALL???  kkqcttcalo()         038450080 044002110 044002110
                                                   043FF23C8

你对可能发生的事情有什么暗示吗？

Answer 1

正如我已经说过的，ORA-03113是一个通用错误。

当发生此错误时，为您的会话提供服务的服务器进程由于后台的其他错误而意外死亡。这些错误将显示在数据库警报日志中。

就你而言：

ORA-07445: [kkqtutlSetViewCols()+250] [ACCESS_VIOLATION] [ADDR:0x8] [PC:0x3DC175C] [UNABLE_TO_READ] []

然后可以使用这些信息搜索support.oracle.com。

这看起来就像

查询失败时，

Bug 9577499或-7445 kkqtutlSetViewCols无法重写.

建议的解决办法是：alter session set query_rewrite_enabled=false;

这在会话级别禁用了该功能，另一种替代方法是仅对抛出此错误并带有提示的SQL语句禁用它：

select /*+ norewrite */ ...

或

select /*+ opt_param('query_rewrite_enabled', 'false') */ ...