ORA-04068的錯誤解決

發布時間: 2012/8/6 17:18:46

今天接到一個奇怪的故障,發現有一些包在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解決該問題.