One of my users is getting this (I have replaced the actual package with the string 'NAMEOFPACKAGE', and the name of the database with 'THEDB'):
Error (-4068)--(ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "THEDB.NAMEOFPACKAGE" has been invalidated
ORA-04065: not executed, altered or dropped package body "THEDB.NAMEOFPACKAGE"
ORA-06508: PL/SQL: could not find program unit being called
Can someone recommend the best way for me to trouble shoot this and figure out what the cause of the problem is? They said they tried to run the application that is generating this error several times.
The 'NAMEOFPACKAGE' is one we have written, and seems to work fine in other databases.
We've seen this problems many times with Oracle Workflow. I don't really know what the cause of the problem is, but it seemed to happen whenever we made a modification to the code (even though everything was compiled correctly). It might have happened on it's own or after some other modification (i wasn't really in charge of workflow).
We were able to work around it by reloading then recompiling all of the invalid objects in whatever package is in error, in our case WFCORE and WFENG. To recompile we just wrote a small script that generated a list of all invalid objects in the database and compiled them, a la
SELECT 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;'
WHERE STATUS = 'INVALID' AND OBJECT_TYPE <> 'PACKAGE BODY'
SELECT 'ALTER PACKAGE '||OWNER||'.'||OBJECT_NAME||' COMPILE BODY;'
WHERE STATUS = 'INVALID' AND OBJECT_TYPE = 'PACKAGE BODY'
so reload whatever packages are complaining, then spool that output to another file, then run it and see if that fixes your problem.
if anybody knows the real reason for this, or how to prevent it, i'd love to hear it.
I will keep you posted. This seems to happen on only one database instance that I know of.
Perhaps it is something to do with how the Oracle was installed. I will let you
know if I find out anything interesting.
Re:existing state has been invalidated.. best way to trouble shoot?
The cause for this is the SGA (memory that is allocated by an Oracle Instance and is shared among Oracle processes). Any object in SGA referencing an object that is compiled become invalid during this compilation.
So in this case the process accessing the compiled object needs to be restarted. For example, in case of Oracle Workflows, this error can be rectified by restarting the Workflow Mailer ( or a Retry also helps). One more way is to bounce the database itself which ofcourse will be a last resort.