ORA-07445: [kkqtutlSetViewCols()+250]

数据库版本： Oracle 11.2.0.1.0

数据库服务器操作系统： Windows server 2012

问题现象：业务人员在前台执行某些查询操作时， ORA-07445: 出现异常错误 : 核心转储 [kkqtutlSetViewCols()+250] [ACCESS_VIOLATION] [ADDR:0x8] [PC:0x3DC175C] [UNABLE_TO_READ] [];

解决方案：查看 alert 警告日志，查找对应的 trace 文件 ;

找到触发此 bug 的 sql 语句如下：

----- Current SQL Statement for this session (sql_id=......) -----

SELECT DISTINCT col_1 col_1_a

  FROM (SELECT col_1, col_2, col_3, concat(col_4, col_5) col_6

          FROM tab_1

         WHERE col_7 IN ('0', '1')

           AND col_8 = 0

           AND nvl(col_9, 5) <> '3') tab_2

  LEFT outer JOIN (SELECT sum(col_1),

                          sum(col_2),

                          col_3,

                          concat(col_4, col_5) col_6

                     FROM tab_6

                    WHERE useflag = 1

                    GROUP BY col_1, col_2)

    ON tab_1.col1 = tab_2.col2

 ......

根据 MOS 查看，和 Bug 9577499 比较匹配，当优化器为了提高查询性能，选择 SQL 进行查询重写 query rewrite 时，会触发此 BUG ；

解决方案：

可以禁用 query rewrite 临时解决此问题，或者 升级数据库跳过此 BUG ；

禁用 query rewrite 语句如下：

alter system set query_rewrite_enabled=false scope=both;---(动态参数)

其中 MOS 中查询有关 ORA-7445 [kkqtutlSetViewCols]] 问题相关信息；

ORA-600/ORA-7445/ORA-700 Error Look-up Tool ( 文档 ID 153788.1)

ORA-7445 [kkqtutlsetviewcols] ( 文档 ID 1085948.1)

In this Document

Oracle Database - Enterprise Edition - Version 7.0.16.0 and later
Oracle Database - Personal Edition
Oracle Database - Standard Edition
Information in this document applies to any platform.

Note: For additional ORA-7445 related information please read  Note:211909.1.

  This article provides information on one or more bugs that are 
  known to result in the given ORA-7445 error.

ERROR:
  ORA-7445 failing function "kkqtutlsetviewcols"

SUGGESTIONS:

  If the Known Issues section below does not help in terms of identifying
  a solution, please submit the trace files and alert.log to Oracle 
  Support Services for further analysis.

  Known Issues:

Known Bugs

You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button: 
          

The list below is restricted to show only bugs believed to affect version 11.2.0.1.
Other bugs may affect this version but have not been confirmed as being relevant yet.

There are 4 bugs listed.

·         '*' indicates that an alert exists for that issue.

·         '+' indicates a particularly notable issue / bug.

·         'I' indicates an install issue / bug included for completeness.

·         'P' indicates a port specific bug.

·         "OERI: xxxx" may be used as shorthand for  ORA-600 [ xxxx].

Bug 9577499 - ORA-7445 [kkqtutlSetViewCols] when query fails to rewrite ( 文档 ID 9577499.8)

Bug 9577499  ORA-7445 [kkqtutlSetViewCols] when query fails to rewrite

 This note gives a brief overview of bug 9577499. 
 The content was last updated on: 28-JUN-2013
  Click  here for details of each of the sections below.

Affects:

Fixed:

Description

A failed attempt to rewrite a query may cause ORA-7445 [kkqtutlSetViewCols]

because the column mapping is not restored correctly. Would usually occur

with inline views.

Workaround

  alter session set query_rewrite_enabled=false;

References

Bug:9577499 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article

  欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！