神奇的ora 04068错误的最终解释【抄】

最新推荐文章于 2022-02-21 11:29:45 发布

wdorld2000

最新推荐文章于 2022-02-21 11:29:45 发布

阅读量446

点赞数

文章标签： service oracle 平台产品 aix ibm

本文链接：https://blog.csdn.net/wdorld2000/article/details/6447671

版权

Oracle Bug 6136074: ORA-04068/ORA-04065/ORA-06508错误解决

2011-01-06 15:04

平台: RHEL4.8 x86_64 + Oracle10.2.0.4

--故障症状提示:

2011-01-05 11:42:44,416 ERROR [com.echargingmid.service.QueryService] ORA-04068: existing state of packages has been discarded

ORA-04068: existing state of packages (YOUJI.LK) has been discarded

ORA-04065: not executed, altered or dropped stored procedure " ERATING.PKG_BAL"

ORA-06508: PL/SQL: could not find program unit being called: "ERATING.PKG_BAL"

ORA-06512: at "ERATING.PKG_CHARGING", line 33

ORA-06512: at "ECHARGING.PKG_SERVICE", line 148

ORA-06512: at "ECHARGING.PKG_SERVICE", line 126

ORA-06512: at line 1

--出现错误后, 做如下查询会得到PKG_BAL包和父对象(即同义词)时间不同步的信息:

SELECT du.name duname,do.name dname, pu.name puname, po.name pname, p_timestamp, po.stime p_stime

FROM sys.obj$ do, sys.dependency$ d, sys.obj$ po,sys.user$ du,sys.user$ pu

WHERE p_obj#=po.obj#(+)

AND d_obj#=do.obj#

AND do.owner# = du.user#

AND po.owner# = pu.user#

AND do.status=1 /*dependent is valid*/

AND po.status=1 /*parent is valid*/

AND po.stime!=p_timestamp /*parent timestamp does not match*/

AND do.type# not in (13,28,29,30) /*dependent type is not a type or java*/

AND po.type# not in (13,28,29,30) /*parent type is not a type or java*/

ORDER BY 4,2;

--产生原因(Oracle Bug 6136074引起, 官方说是10.2.0.4版本中已经解决, 实际上根本没解决):

These packages are all in one schema, and there are private synonyms pointing to them in each schema that must run them.

it has been found that these private synonyms are out of sync, and this is causing the errors ORA-04065 ORA-06508.

--解决办法(编译所有时间不同步的同义词):

$ sqlplus / as sysdba

SQL> declare

cursor cur_syn is

select do.name d_name, u.name owner

from sys.obj$ do, sys.dependency$ d, sys.obj$ po, user$ u

where P_OBJ#=po.obj#(+)

and D_OBJ#=do.obj#

and do.status=1 /*dependent is valid*/

and po.status=1 /*parent is valid*/

and po.stime!=p_timestamp /*parent timestamp not match*/

and do.owner#=u.user#

and do.type# = 5

order by 2,1;

v_syn_name obj$.name%TYPE;

v_tab_own user$.name%TYPE;

begin

OPEN cur_syn;

loop

FETCH cur_syn INTO v_syn_name,v_tab_own;

exit when cur_syn%notfound;

if v_tab_own = 'PUBLIC' then

execute immediate 'alter public synonym "'||v_syn_name|| '" compile';

else

execute immediate 'alter synonym '||v_tab_own||'.'||v_syn_name|| ' compile';

end if;

end loop;

CLOSE cur_syn;

end;

--编译完后, 检查是否还有不同步的同义词:

SQL> set pagesize 10000

set linesize 140

column d_name format a20

column p_name format a20

SQL> select do.obj# d_obj,do.name d_name, u.name owner, po.obj# p_obj,po.name p_name,

to_char(p_timestamp,'DD-MON-YYYY HH24:MI:SS') "P_Timestamp",

to_char(po.stime ,'DD-MON-YYYY HH24:MI:SS') "STIME",

decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X