java 调用 ora-04068_ORA-04068的错误解决

今天接到一个奇怪的故障,发现有一些包在NODE1上执行是正常的,再NODE2上执行报ORA-04068的错误.但是检查DBA_INVALIED_OBJECTS的STATUS都是正常.(由于昨天晚上对数据库的某些PACKAGE进行修改)

后来检查包的依赖情况,发现有很多依赖包的时间戳已经不一致导致该问题的.

使用以下命令可以检查到PACKAGE的依赖情况:

set pagesize 10000

column d_name format a20

column p_name format a20

select do.obj# d_obj,do.name d_name, do.type# d_type,

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

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

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*/

order by 2,1;

如果检查出有依赖包时间戳不一致,则需要重新编译该包

可以使用以下命令进行编译:

DECLARE

CURSOR c_sql

IS

SELECT DISTINCT

'alter '

|| DECODE (do.type#,

12, 'TRIGGER',

4, 'VIEW',

5, 'SYNONYM',

7, 'PROCEDURE',

8, 'FUNCTION',

9, 'PACKAGE',

11, 'PACKAGE')

|| ' '

|| u.name

|| '.'

|| do.name

|| ' '

|| DECODE (do.type#,

12, 'compile',

4, 'compile',

5, 'compile',

7, 'compile',

8, 'compile',

9, 'compile package',

11, 'compile body')

sql_text

FROM sys.obj$ do,

sys.dependency$ d,

sys.obj$ po,

sys.user$ u

WHERE     P_OBJ# = po.obj#(+)

AND D_OBJ# = do.obj#

AND do.status = 1

AND po.status = 1

AND do.owner# = u.user#

AND po.stime != p_timestamp;

v_sql_text   VARCHAR (2000);

BEGIN

FOR v_sql IN c_sql

LOOP

v_sql_text := v_sql.sql_text;

DBMS_OUTPUT.put_line (v_sql_text);

EXECUTE IMMEDIATE v_sql_text;

END LOOP;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

DBMS_OUTPUT.put_line (v_sql_text);

END;

该问题是由于11.2.0.2版本上个一个BUG 13328947,可以打PATCH:9681133解决该问题.