Hi All,
Shot in the dark question here.
We have a situation which has happened repeatedly over the last week or so with a set of core PL/SQL package bodies going into an invalid state during our online day.
We have DDL auditing enabled and can see that NOTHING which these objects are dependant upon (from DBA_DEPENDENCIES) has been changed, either locally or remotely, I have also checked that there are no timestamp mismatches between sys.dependency$ and sys.obj$ - again all clear.
So, does anyone know of any under the covers method of enabling a form of tracing which might reveal why the DB is marking the objects invalid ? We cannot SQL trace as there are thousands of connection pooled .NET sessions hitting the DB, more often than not these are the sessions which have the issues (they all try to auto recompile and end up in a horrible mess of library cache locks).
I was secretly hoping for an "event 99999" which generates a nice tracefile every time a package is marked as invalid and explains why ... probably asking too much!
Any thoughts would be welcomed though.
Many Thanks,
Paul