While I'm aware the ORA-06508 error is caused by a missing (or) invalid package, we have a scenario wherein -
Package XXX when executed throws "ORA-06508:PL/SQL: could not find program unit being called" error. Taking a look at the log messages from the execution of this package, we identified the issue to be due to another package YYY which is invoked from XXX. However, package YYY is present in the database, is valid, and has not been modified in the last few weeks.
What am I missing here ? Appreciate your advice.
I'm afraid I cannot give steps to reproduce the problem, as it is not reproducible at will - occurs on and off, only with one package, and unfortunately only in the live instance.
Another curious thing is that a bounce of the middle tier seems to solve the problem (package XXX executed successfully afterward) - hope that tells you something I can't see for myself.
We are using single instance Oracle database.
Quick question - are you running single instance Oracle or using RAC ?
OK (the reason for the RAC question, was there have been bugs in the past with PLSQL spanning RAC instances, but we can rule that out now).
A good thing to check to make sure that things definitely are not changing is the LAST_DDL_TIME on objects in your database. Dont forget, you might do a DDL on a *table*, which then marks package "invalid" (ie, requiring recompilation). That should happen automatically on next invocation, but if there's some sort of issue with that, then maybe manual checks on package object status after DDL will solve your issue.
Otherwise, there are a couple of support notes regarding erroneous reporting of ora-6508 pertaining to synonyms being "out of sync" with their underlying objects.
The suggestion is to try the following (both as SYSDBA)
1) compilation
declare
cursor cur_syn is select do.name d_name, u.name owner
from sys.obj$ do, sys.dependency$ d, sys.obj$ po, user$ u
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*/
and do.owner#=u.user#
and do.type# = 5
order by 2,1;
v_syn_name obj$.name%TYPE;
v_tab_own user$.name%TYPE;
begin
OPEN cur_syn;
loop
FETCH cur_syn INTO v_syn_name,v_tab_own;
exit when cur_syn%notfound;
if v_tab_own = 'PUBLIC' then
execute immediate 'alter public synonym "'||v_syn_name|| '" compile';
else
execute immediate 'alter synonym '||v_tab_own||'.'||v_syn_name|| ' compile';
end if;
end loop;
CLOSE cur_syn;
end;
/
2) check for any sync errors
set pagesize 10000
set linesize 140
column d_name format a20
column p_name format a20
select do.obj# d_obj,do.name d_name, u.name owner, 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, user$ u
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*/
and do.owner#=u.user#
and do.type# = 5
order by 2,1;
If that doesnt help, I think you might need to take it up with Support.