今天接到一个奇怪的故障,发现有一些包在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解决该问题.