Frequent error in Oracle ORA-04068: existing state

问题:

We're getting this error once a day on a script that runs every two hours, but at different times of the day.

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "PACKAGE.NAME" has been
invalidated
ORA-06508: PL/SQL: could not find program unit being called:
"PACKAGE.NAME"
ORA-06512: at line 1

Could someone list what conditions can cause this error so that we could investigate?

Thanks.

UPDATE: Would executing 'ALTER SESSION CLOSE DATABASE LINK DBLINK' invalidate a state of the package?

回答1:

The package has public or private variables. (Right?) This variables form the state a the package. If you compile the package in 3rd session. The next access to this package will throw the ORA-04068.

The build timestamp of a package must be older than the package session state.

If the package state is not needed for script running, the call DBMS_SESSION.RESET_PACKAGE at the beginning of your script. This cleans all package states of your session.

回答2:

This one liner actually solved everything:

PRAGMA SERIALLY_REUSABLE;

Be sure that your global variables are stateless to avoid any issues.

回答3:

You may also check dba_dependencies or user_dependencies.

select *
from dba_dependencies
where name = 'YOUR_PACKAGE'
and type = 'PACKAGE' --- or 'PACKAGE_BODY'
and owner = USER --- or USERNAME

This will give you the objects your package is dependent on. Check whats happening in there.

回答4:

We have had this issues for couple of times and for time being, we were compiling schema to resolve this issue temporarily. Over couple of days we were searching for the permanent resolution.

We found below query that showed timestamp difference in our synonym. we recompiled synonym and It worked !!! It's been almost a week and so far we have no issues. Here is the query that helped in our case.

**

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;

**

I hope this helps someone who may be having this issue.

回答5:

It seems that you are making changes to your objects that make other objects invalid. Droping an index for example can put into an invalid state all the packages that dependes on that table. It can have a cascade efect. If the package is invalid, the funciton that depends on the package and the view that uses the function can become invalid. Try to recompile all the objects after every DDL query.