Chris, Maybe there is some code, running now and then, that uses NDS (execute immediate) to execute some DDL, that in turn invalidates a package body of which sessions hold state. This code would not show up in DBA_DEPENDENCIES... It would give the symptoms you describe. Just a thought. Toon On Thu, Mar 12, 2009 at 4:39 PM, Stephens, Chris < chris_stephens@xxxxxxxxxxxx> wrote: > I’ve gotten several private emails that demonstrate I didn’t clearly > explain the problem I’m having. > > > > The package compiles fine once recompiled. It is my understanding that > when an attempt is made to execute a procedure or function contained in the > package, automated compiling should take place and the executor should be > non the wiser. This is not what we are seeing. Instead the application > receives the error: > > > > ORA-04068: existing state of packages has been discarded ORA-04061: > existing state of package body "PROJECT.PCKG_CHARGECODE" has been > invalidated ORA-04065: not executed, altered or dropped package body > "PROJECT.PCKG_CHARGECODE" > > > > > > We have added an exception handler in the code to recompile the package > with “execute immediate ‘alter package xxxx compile’;” that prevents the > user from seeing the error but I would like to prevent this from being > necessary in the first place. > > > > Also, am I wrong in expecting the compilation to happen automatically? > > > > This is a 10.2.0.4 database on RHEL 5 by the way. > > > > *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto: > oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Stephens, Chris > *Sent:* Thursday, March 12, 2009 10:24 AM > *To:* oracle-l@xxxxxxxxxxxxx > *Subject:* source of package invalidation > > > > We are seeing packages invalidated in our production database and I don’t > have a clue how to track down the actual cause. > > > > I’ve taken a look at dba_dependencies and know the potential candidates for > causing the invalidation but I’m looking to actually identify the chain of > events that is causing this. > > > > Is there an oracle event that can be set to generate a trace file for > something like this? > > > > Any other ideas on how to track this down? > > > > Thanks! > > Chris > > > > > > > CONFIDENTIALITY NOTICE: > This message is intended for the use of the individual or entity to which > it is addressed and may contain information that is privileged, > confidential and exempt from disclosure under applicable law. If the reader > of this message is not the intended recipient or the employee or agent > responsible for delivering this message to the intended recipient, you are > hereby notified that any dissemination, distribution or copying of this > communication is strictly prohibited. If you have received this > communication in error, please notify us immediately by email reply. > > > > CONFIDENTIALITY NOTICE: > This message is intended for the use of the individual or entity to which > it is addressed and may contain information that is privileged, > confidential and exempt from disclosure under applicable law. If the reader > of this message is not the intended recipient or the employee or agent > responsible for delivering this message to the intended recipient, you are > hereby notified that any dissemination, distribution or copying of this > communication is strictly prohibited. If you have received this > communication in error, please notify us immediately by email reply. > > > -- Toon Koppelaars RuleGen BV +31-615907269 Toon.Koppelaars@xxxxxxxxxxx http://www.RuleGen.com http://thehelsinkideclaration.blogspot.com (co)Author: "Applied Mathematics for Database Professionals" http://www.rulegen.com/pls/apex/f?p=14265:13