oracle01900,ORA-00600: 内部错误代码, 参数: [19004]

ORA-00600:

内部错误代码

,

参数

: [19004]

数据库版本：

Oracle

11.2.0.1.0

数据库服务器操作系统：

Windows

server 2008

问题现象：业务人员在前台执行某些查询操作时，报错

ORA-00600:

内部错误代码

,

参数

: [19004]

；

解决方案：查看

alert

警告日志，查找对应的

trace

文件，找到触发此

bug

的

sql

语句如下：

----- Current SQL Statement for this session

(sql_id=.......) -----

SELECT *

FROM TAB_1

WHERE

COL_1 in (select id from TAB_2)

and COL_2

in (:1, :2)

and COL_3

in

('null', 'null', 'null', 'null', 'null', 'null', 'null')

order by COL_4,COL_5;

根据

MOS

查看，此错误匹配多个

BUG

，都是由于表错误的统计信息和列的统计直方图触发此

BUG

，可以删除

SQL

中对应表的统计信息和统计直方图临时解决此问题，或者直接升级数据库跳过此

BUG

；

删除统计信息方法如下：

---ANALYZE TABLE TAB_1 DELETE STATISTICS;

---ANALYZE TABLE TAB_2 DELETE STATISTICS;

SQL>

execute dbms_stats.delete_table_stats(ownname=>'USER_NAME',tabname=>'TAB_1');

SQL>

execute dbms_stats.delete_table_stats(ownname=>'USER_NAME',tabname=>'TAB_2');

SQL>

execute dbms_stats.lock_table_stats(ownname=>'USER_NAME',tabname=>'TAB_1');

SQL>

execute dbms_stats.lock_table_stats(ownname=>'USER_NAME',tabname=>'TAB_2');

---SQL>

execute dbms_stats.unlock_table_stats(ownname=>'USER_NAME',tabname=>'TAB_1');

---SQL>

execute dbms_stats.unlock_table_stats(ownname=>'USER_NAME',tabname=>'TAB_2');

删除表统计信息可能会生成错误的执行计划，影响

SQL

执行效率；

可以适当调大动态取样级别；

SQL> show

parameter optimizer_dynamic_sampling

NAME                                 TYPE        VALUE

------------------------------------

----------- ------------------------------

optimizer_dynamic_sampling           integer     4

其中

MOS

中查询有关

ORA-00600[19004]

问题相关信息；

ORA-600/ORA-7445/ORA-700 Error Look-up Tool (

文档

ID 153788.1)

ORA-600 [19004] (

文档

ID 138652.1)

Note: For additional ORA-600 related

information please read

Note:146580.1

PURPOSE:

This

article represents a partially published OERI note.

It

has been published because the ORA-600 error has been

reported in at least one confirmed bug.

Therefore, the SUGGESTIONS section of this article may help

in

terms of identifying the cause of the error.

This

specific ORA-600 error may be considered for full publication

at a

later date. If/when fully published, additional information

will

be available here on the nature of this error.

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:

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 6

bugs listed.

NB

Prob

Bug

Fixed

Description

II

12.1.0.2, 12.2.0.0

ORA-600 [19004] when stats have been gathered with

NLS_SORT / NLS_COMP not default BINARY

II

11.2.0.4, 12.1.0.1

Manually setting column stats cannot exceed 4Gb max

size

III

12.1.0.2, 12.2.0.0

ORA-600 [19004] or inaccuracies on join of histogrammed

columns

III

11.2.0.3, 12.1.0.1

Allow optimizer to bypass corrupt histogram data

III

11.2.0.2, 12.1.0.1

Query optimization fails with OERI[19004]

E

II

11.2.0.2, 12.1.0.1

Enh: allow extended and adaptive cursor sharing to

support LIKE predicates

·

'*' indicates

that an alert exists for that issue.

·

'+' indicates

a particularly notable issue / bug.

·

See

Note:1944526.1 for details of other symbols used

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