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

本文介绍了在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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值