ORA-07445： exception encountered： core dump [evaopn2()+2628]

最新推荐文章于 2023-10-13 19:11:03 发布

转载最新推荐文章于 2023-10-13 19:11:03 发布 · 197 阅读

本文记录了在执行Oracle存储过程时遇到的ORA-03113和ORA-07445异常现象及解决过程。通过调整SQL语句避免使用特定功能索引，成功解决了异常断开连接的问题。

os version: linux
oracle version: oracle 9204
-----------------------------------------------------------------------------------------

When I execute a sql in procedure ,It disconnect. It’s amazing.see that:

SQL> conn ngcc_best/ngcc_Best@zxin_116

已连接。

SQL> var v_i number;

SQL> exec pro_auto_IsInBefore_te1('18999912345',:v_i);

BEGIN pro_auto_IsInBefore_te1('18999912345',:v_i); END;

第 1 行出现错误:

ORA-03113: 通信通道的文件结束

Actually, the result is 1 .the detail content of procedure “pro_auto_IsInBefore_te1” is that:

create or replace procedure pro_auto_IsInBefore_te1

(

p_strcallingno IN varchar2, --主叫号码号段部分3-15位才能匹配有效

p_IsIn OUT integer --标志

)

v_count integer;

Begin

select count(1) into v_count from

(

select strdhhm from t_auto_customerjudge

where ltrim(p_strcallingno, '0') = ltrim(strdhhm, '0')

and type = '1'

and (sysdate - dtjudgedate) * 1440 <= inthmdlimit

and inthmdflag = 1

union all

SELECT strdhhm from t_auto_customerjudge a where

(

substr(ltrim(p_strcallingno, '0'), 1, 3) = ltrim(strdhhm, '0')

substr(ltrim(p_strcallingno, '0'), 1, 4) = ltrim(strdhhm, '0')

)

and type = '2'

and (sysdate - dtjudgedate) * 1440 <= inthmdlimit

and inthmdflag = 1

);

if v_count = 0 then

p_IsIn:=0;

else

p_IsIn:=1;

end if;

End ;

Table t_auto_customerjudge has a function index.The definition of the index is that:

create index IND_AUTO_CUSTOMERJUDGE_LTRIM on T_AUTO_CUSTOMERJUDGE (LTRIM(STRDHHM,'0')) tablespace TSP_NGCC_BEST;

Table has been analyzed.if you execute the statement independed of the procedure.it do well.

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

SQL> select count(1) from

2 (

3 select strdhhm from t_auto_customerjudge

4 where ltrim('18999912345', '0') = ltrim(strdhhm, '0')

5 and type = '1'

6 and (sysdate - dtjudgedate) * 1440 <= inthmdlimit

7 and inthmdflag = 1

8 union all

9 SELECT strdhhm from t_auto_customerjudge a where

10 (

11 substr(ltrim('18999912345', '0'), 1, 3) = ltrim(strdhhm,

'0')

12 or

13 substr(ltrim('18999912345', '0'), 1, 4) = ltrim(strdhhm,

'0')

14 )

15 and type = '2'

16 and (sysdate - dtjudgedate) * 1440 <= inthmdlimit

17 and inthmdflag = 1

18 );

COUNT(1)

----------

So,why this happen?

When I check the alert.log,I found that message:

Thu Sep 9 17:56:42 2010

Errors in file /home/oracle/zxindbf/admin/zxin/udump/zxin_ora_2508.trc:

ORA-07445: exception encountered: core dump [evaopn2()+2628] [SIGSEGV] [Address not mapped to object] [0x0] [] []

Thu Sep 9 21:55:12 2010

Errors in file /home/oracle/zxindbf/admin/zxin/udump/zxin_ora_5393.trc:

ORA-07445: exception encountered: core dump [evaopn2()+2628] [SIGSEGV] [Address not mapped to object] [0x0] [] []

Thu Sep 9 21:56:59 2010

Errors in file /home/oracle/zxindbf/admin/zxin/udump/zxin_ora_5437.trc:

ORA-07445: exception encountered: core dump [evaopn2()+2628] [SIGSEGV] [Address not mapped to object] [0x0] [] []

Thu Sep 9 22:01:22 2010

Errors in file /home/oracle/zxindbf/admin/zxin/udump/zxin_ora_5557.trc:

ORA-07445: exception encountered: core dump [evaopn2()+2628] [SIGSEGV] [Address not mapped to object] [0x0] [] []

Thu Sep 9 22:02:36 2010

Errors in file /home/oracle/zxindbf/admin/zxin/udump/zxin_ora_5645.trc:

ORA-07445: exception encountered: core dump [evaopn2()+2628] [SIGSEGV] [Address not mapped to object] [0x0] [] []

It seems that when I execute the procedure “pro_auto_IsInBefore_te1”,the error 07445 will generate.

Then I check the trace file /home/oracle/zxindbf/admin/zxin/udump/zxin_ora_5557.trc:

the important information is that:

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

ksedmp: internal or fatal error

ORA-07445: exception encountered: core dump [evaopn2()+2628] [SIGSEGV] [Address not mapped to object] [0x0] [] []

Current SQL statement for this session:

SELECT count(1) from

(

select strdhhm from t_auto_customerjudge

where ltrim(:b1, '0') = ltrim(strdhhm, '0')

and type = '1'

and (sysdate - dtjudgedate) * 1440 <= inthmdlimit

and inthmdflag = 1

union all

select strdhhm from t_auto_customerjudge where

(

substr(ltrim(:b1, '0'), 1, 3) = ltrim(strdhhm, '0')

substr(ltrim(:b1, '0'), 1, 4) = ltrim(strdhhm, '0')

)

and type = '2'

and (sysdate - dtjudgedate) * 1440 <= inthmdlimit

and inthmdflag = 1

)

----- PL/SQL Call Stack -----

object line object

handle number name

0xa3d1627c 9 procedure NGCC_BEST.PRO_AUTO_ISINBEFORE_TE

0xa5b6cd5c 1 anonymous block

So,this sql in procedure makes the error 07455.how It make the error?

After some test,I found that when the function index is in use in the subquery statement:

select strdhhm from t_auto_customerjudge where

(

substr(ltrim(:b1, '0'), 1, 3) = ltrim(strdhhm, '0')

substr(ltrim(:b1, '0'), 1, 4) = ltrim(strdhhm, '0')

)

and type = '2'

and (sysdate - dtjudgedate) * 1440 <= inthmdlimit

and inthmdflag = 1

)

It will makes the error.and if the subquery goes “table access full”,the error does not hppend.

Metalink says this may be a bug.But, when we consider a error is a bug,you must make sure that this error happen very common,and creating SR is a good Idea.

So,I change the sql in the procedure.like that :

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

create or replace procedure pro_auto_IsInBefore_te1

(

p_strcallingno IN varchar2, --主叫号码号段部分3-15位才能匹配有效

p_IsIn OUT integer --标志

)

v_count integer;

Begin

select count(1) into v_count from

(

select strdhhm from t_auto_customerjudge

where ltrim(p_strcallingno, '0') = ltrim(strdhhm, '0')

and type = '1'

and (sysdate - dtjudgedate) * 1440 <= inthmdlimit

and inthmdflag = 1

union all

SELECT strdhhm from t_auto_customerjudge a where

(

ltrim(strdhhm, '0') IN (substr(ltrim(p_strcallingno, '0'), 1, 3),

substr(ltrim(p_strcallingno, '0'), 1, 4))

)

and type = '2'

and (sysdate - dtjudgedate) * 1440 <= inthmdlimit

and inthmdflag = 1

);

if v_count = 0 then

p_IsIn:=0;

else

p_IsIn:=1;

end if;

End ;

Now ,It goes well.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14730395/viewspace-673111/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14730395/viewspace-673111/