定时任务出错，ORA-06508 PL/SQL: could not find program unit being called

定时任务出错，ORA-06508 PL/SQL: could not find program unit being called: "MREAD.PRC_IREAD_SYS_WRITELOG"
ORA-12012: error on auto execute of job 462028
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "MREAD.PRC_IREAD_SYS_WRITELOG"
ORA-06508: PL/SQL: could not find program unit being called: "MREAD.PRC_IREAD_SYS_WRITELOG"
ORA-06512: at "MREAD.P_CONSUMPTION_EXECUTE", line 122
ORA-04065: not executed, altered or dropped stored procedure "MREAD.PRC_IREAD_SYS_WRITELOG"
ORA-06508: PL/SQL: could not find program unit being called: "MREAD.PRC_IREAD_SYS_WRITELOG"
ORA-06512: at line 3

--检查MOS文档，上述ORA报错原因
ORA-06508:    PL/SQL: could not find program unit being called
Cause:    An attempt was made to call a stored program that could not be found. The program may have been dropped or incompatibly modified, or have compiled with errors.
Action:    Check that all referenced programs, including their package bodies, exist and are compatible.

--这个问题对应的BUG信息为：Bug 6136074 - ORA-4068 / ORA-4065 ORA-6508 on VALID objects [ID 6136074.8]，导致问题的原因就是编译对象时导致PLSQL的依赖对象的时间戳发生不一致，从而导致问题的产生。 Oracle文档上给出了检查问题的SQL语句：
select do.obj# d_obj,do.name named, 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;

       D_OBJ    NAMED    D_TYPE    P_OBJ    P_NAME    P_Timestamp    STIME    X
1    1689820    P_ADDBILL    7    123972    PRC_IREAD_SYS_WRITELOG    21-9月 -2010 00:54:16    03-7月 -2013 04:48:47    *DIFFER*

--重新编译上述存储过程
ALTER PROCEDURE P_ADDBILL COMPILE;
ALTER PROCEDURE PRC_IREAD_SYS_WRITELOG COMPILE;

--测试原始出错存储过程，发现错误依然存在
select * from tbl_iread_sys_log where s_procname='p_consumption_execute';

declare retcode varchar2(11);
begin
    mread.p_consumption_execute(sysdate-1,retcode);
    DBMS_OUTPUT.PUT_LINE(retcode);
end;
/

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure
"MREAD.PRC_IREAD_SYS_WRITELOG"
ORA-06508: PL/SQL: could not find program unit being called:
"MREAD.PRC_IREAD_SYS_WRITELOG"
ORA-06512: at "MREAD.P_CONSUMPTION_EXECUTE", line 122
ORA-04065: not executed, altered or dropped stored procedure
"MREAD.PRC_IREAD_SYS_WRITELOG"
ORA-06508: PL/SQL: could not find program unit being called:
"MREAD.PRC_IREAD_SYS_WRITELOG"
ORA-06512: at line 3

--检查子存储过程MREAD.PRC_IREAD_SYS_WRITELOG
select * from all_source where name = 'PRC_IREAD_SYS_WRITELOG';

SELECT object_name, object_type, owner, status, last_ddl_time FROM
       dba_objects WHERE object_name = 'PRC_IREAD_SYS_WRITELOG';
       
SELECT name, type, text FROM dba_errors WHERE name = 'PRC_IREAD_SYS_WRITELOG';

SELECT owner, table_name, privilege FROM dba_tab_privs WHERE
       table_name = 'TBL_IREAD_SYS_LOG';

begin
    prc_iread_sys_writelog(2, 4, 'test', 'errorinfo', '');
end;

select * from tbl_iread_sys_log where s_procname='test';
成功但没有输出
delete tbl_iread_sys_log where s_procname='test';

select *
  from v$locked_object
 where object_id in (select object_id
                       from dba_objects
                      where object_name = 'TBL_IREAD_SYS_LOG'
                        and owner = 'MREAD');

分析：
prc_iread_sys_writelog是一个核心存储过程，绝大部分存储过程都需要调用其，将日志信息写入TBL_IREAD_SYS_LOG表。当大量存储过程同时运行时，都会调度prc_iread_sys_writelog，存储过程/匿名sql每次都需要解析，因此在shared_pool中产生了Library Cache locks。当shared_pool占满后，后续存储过程就产生相关pin和lock。

解决：新建一个存储过程prc_iread_sys_writelog1和表TBL_IREAD_SYS_LOG1，将部分存储过程的日志分流到新的存储过程中。
经过数天观察，问题未重现。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/692830/viewspace-1218616/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/692830/viewspace-1218616/