A developer batch job runs every Wednesday at 8 pm. It calls a package and runs for about an hour. The package exists for the whole week until this batch job is run, and come the next morning, Thursday, when it's called again by other processes during the day, it sends out an error saying that it does not exist. The developer swears that he doesn't see the package as invalid as he has checked when the error occurs and it is dropped by something or someone. This has now happened two weeks in a row. I cannot find it in the redo log as a dropped object for some reason and have exhausted most avenues that I know on how to look for this. The developer has to re-create it using the source code. Can you suggest what else I could try? The error he gets is:
ERROR at line 1: ORA-04068: existing state of packages has been discarded
ORA-04067: not executed, package body "WRAPSUPP.PMM_TRANSACTIONS" does not exist
ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 1
It seems to me that someone is dropping your package and you'd like to find out who it is. I'd turn on auditing for this and then I'd look at the audit trail (DBA_AUDIT_TRAIL) to see who the culprit is.
AUDIT DROP PACKAGE ON WRAPSUPP.PMM_TRANSACTIONS WHENEVER SUCCESSFUL;
Once you know who is dropping the package, you can take appropriate actions to reverse this course of action.
Alternatively, you can create a trigger which stops the DROP PACKAGE from occurring.