ORACLE10G 出现00600的一个BUG的解决方案

最新推荐文章于 2024-12-30 10:00:54 发布

原创最新推荐文章于 2024-12-30 10:00:54 发布 · 1.8k 阅读

0 ·

CC 4.0 BY-SA版权

Oracle 错误解决同时被 2 个专栏收录

45 篇文章

订阅专栏

Ora-600 错误

2 篇文章

订阅专栏

本文介绍了在Oracle 10.1到10.2版本中，进行多表关联复杂查询时出现ORA-00600错误的问题及解决办法。错误通常发生在涉及大量聚合和多表连接的SQL查询中。文中提供了两种解决方案，并提到移除直方图统计信息是一种可行的工作绕过方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天又一同事遇到一个oracle数据库bug问题，如下：

--ORACLE 10.1 OR 10.2中所有平台都存在该问题.

<问题现像>
在进行多表关联复杂查询时出现
ORA-00600: 内部错误代码, 参数: [19004], [], [], [], [], [], [], []
错误

<解决办法>
这是一个ORACLE的BUG
处理办法1:
以DBA身份执行:
execute dbms_stats.delete_schema_stats('schema owner');

处理办法2:
如果只有个别表在查询时出现错误,可以只对出错表理行处理
execute dbms_stats.delete_table_stats('schema owner','table');

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------此问题的英文资料：

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:
Oracle Server - Enterprise Edition - Version: 10.1 to 10.2
This problem can occur on any platform.

Symptoms
Alert log reports the following errors several times:
ORA-00600: internal error code, arguments: [19004], [], [], [], [], [], [], []

Trace file reports current SQL executed via SQLPlus which includes many aggregates and multijoins:
SELECT bill_rd_id_1_label,ultimate_orig_15_label,ultimate_orig_9_label,ultimate_orig_2_label, SUM(
VALUE_1 ),
SUM( VALUE_2 ),SUM( VALUE_3 ),SUM( VALUE_4 ),SUM( VALUE_5 ),SUM( VALUE_6 ),SUM( VALUE_7 ),
SUM( VALUE_8 ),SUM( VALUE_9 ),SUM( VALUE_10 ),SUM( VALUE_11 ),SUM( VALUE_12 ),SUM( VALUE_13 ),SUM(
VALUE_14 ),SUM( VALUE_15),.....

'136 - LONG LOG FLAT','138 - PLAIN AND EQUP FLAT','139 - HEAVY DUTY FLAT','140 - FRAME AND
PEDESTAL FLAT',
'141 - CENTER BEAM FLAT','142 - OTHER CAR TYPE','143 - BI LEVEL FLAT','144 - TRI LEVEL FLAT','145
- IM CONVENTIONAL CAR (P)',
'146 - IM ARTICULATED SPINE CAR (Q)','147 - TRAILERS','148 - CONTAINERS','149 - TANK CAR','150 -
LOCOMOTIVE','151 - MOW CAR',
'152 - CABOOSE','155 - ROAD RAILERS AND CHASIS','160 - IM STACK CAR (S)','180 - WET ROCK
HOPPER','UNKNOWN','unknown CAR_TYPE_3')
AND driver_cube_300.car_type_key=valid_car_type_300.car_type_key
AND driver_cube_300.bill_rd_id_key=valid_bill_rd_id_300.bill_rd_id_key
AND driver_cube_300.ultimate_orig_key=valid_ultimate_orig_300.ultimate_orig_key
AND driver_cube_300.lob_key=valid_lob_300.lob_key
AND driver_cube_300.time_key=valid_car_type_300.time_key
AND driver_cube_300.time_key=valid_bill_rd_id_300.time_key
AND driver_cube_300.time_key=valid_ultimate_orig_300.time_key
AND driver_cube_300.time_key=valid_lob_300.time_key
AND driver_cube_300.time_key=periods.time_key
AND driver_cube_300.time_key IN ( '33','34','35')
GROUP BY bill_rd_id_1_label,ultimate_orig_15_label,ultimate_orig_9_label,ultimate_orig_2_label
ORDER BY bill_rd_id_1_label,ultimate_orig_15_label,ultimate_orig_9_label,ultimate_orig_2_label
Cause
Unpublished Bug 4899105 - Multitable join could get ORA-600[19004], if some of join columns have histograms.
Fixed In Ver: 11.0
Workaround:
gather statistics without histograms

1. Stack (kkejeq kkepsl kkeidc kketac kkonxc kkotap) matches Bug 5041016 closed as duplicate of Bug 4899105.

2. Multitable join matches condition of internal Bug 4899105

Solution
1. Implement Workaround

Workaround A:

Regather statistics without histograms

There is no way to only remove the histograms. You would need to re-collect statistics without the histograms.

Using dbms_stats package - you would spe