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解决该问题.