ORA-00600错误及其解决方案

ORA-00600错误及其解决方案

错误描述：

数据库版本：

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

错误现象：

执行一条sql语句后出现错误，其中fn_datatoipstr是一个数字转IP串的函数，

fn_ipstrtodata是一个ip字符串转数字的函数。

SQL> select rownum rn ,t.* from (select filehash,filename,updatetime,fn_datatoipstr(peerip) peerip from t_edonkey_temp_peers_info where 1 =1 and fn_ipstrtodata(fn_datatoipstr(peerip)) >= 3396543741 order by updatetime desc) t;

select rownum rn ,t.* from (select filehash,filename,updatetime,fn_datatoipstr(peerip) peerip from t_edonkey_temp_peers_info where 1 =1 and fn_ipstrtodata(fn_datatoipstr(peerip)) >= 3396543741 order by updatetime desc) t

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

在调试页面时出现如下错误：

分析错误原因：

以前使用Oracle10.1g版本就没有出现此错误。初步分析是数据库版本不同造成的。

通过查询相关资料发现了一个相关的数据库参数：_optimizer_filter_pred_pullup

use cost-based flter predicate pull up transformation

（资料来源：http://www.orafaq.com/parms/parm1457.htm）

optimizer_filter_pred_pullup
Oracle 11.1.0:
Parameter Name:
 _optimizer_filter_pred_pullup
 
Description:
 use cost-based flter predicate pull up transformation
 
Type:
 BOOL
 Obsoleted:
 FALSE
 
Can ALTER SESSION:
 TRUE
 Can ALTER SYSTEM:
 IMMEDIATE
 

Oracle 10.2.0:
Parameter Name:
 _optimizer_filter_pred_pullup
 
Description:
 use cost-based flter predicate pull up transformation
 
Type:
 BOOL
 Obsoleted:
 FALSE
 
Can ALTER SESSION:
 TRUE
 Can ALTER SYSTEM:
 IMMEDIATE
 

Oracle 10.1.0:
No such parmeter in Oracle 10.1.0.

use cost-based flter predicate pull up transformation

Predicate pullup does not typecheck operands properly leading
to dumps / internal errors (eg: ORA-600 [evapls1]) at execution time.

解决方案：

alter session set "_optimizer_filter_pred_pullup"=false;

alter system set "_optimizer_filter_pred_pullup"=false;