Re: source of package invalidation

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_at_admworld.com> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Stephens, Chris
> *Sent:* Thursday, March 12, 2009 10:24 AM
> *To:* oracle-l_at_freelists.org
> *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_at_RuleGen.com
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

--
http://www.freelists.org/webpage/oracle-l