RE: How to determine sessions with invalid package states

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Toon Koppelaars Sent: Thursday, May 07, 2009 10:52 AM
To: rgravens_at_gmail.com
Cc: oracle-l-freelists
Subject: Re: How to determine sessions with invalid package states

I work around this issue.

The way I work around this issue is that I introduce dedicated state-only packages. All packages with code are not allowed to have package-variables. The state-only packages have no further dependencies, they hold no code: i.e. their bodies will never get invalidated by newly replaced other objects.

And,

Since they hold no code, these state-only packages hardly ever require replacing themselves. Only when new state-variables are introduced.

In doing so I reduce the number of occurences of this issue, dramatically.

On Thu, May 7, 2009 at 4:35 PM, Rumpi Gravenstein <rgravens_at_gmail.com<mailto:rgravens_at_gmail.com>> wrote: I received a couple of responses on this -- the responses were around looking at locks. That will not help. I'm attempting to find packages that have invalid session state. The scenario is like so

Session one calls package TEST that creates session state A

Session two recompiles package TEST. If session one had a lock, this would not be possible. At this point session one's package state is invalid.

Session one calls package TEST and receives something along the lines of: ORA-04068: existing state of packagesstringstringstring has been discarded

On Wed, May 6, 2009 at 1:27 PM, Rumpi Gravenstein <rgravens_at_gmail.com<mailto:rgravens_at_gmail.com>> wrote: All,

Is it possible to write a query to identify sessions that are holding invalidated package states? We are looking at issues around code migrations. The goal is to only recycle sessions that we know will have problems. Any ideas?

--

Rumpi Gravenstein

--

Rumpi Gravenstein

--

Toon Koppelaars
RuleGen BV
+31-615907269
Toon.Koppelaars_at_RuleGen.com
www.RuleGen.com<http://www.RuleGen.com>
TheHelsinkiDeclaration.blogspot.com<http://TheHelsinkiDeclaration.blogspot.com>

(co)Author: "Applied Mathematics for Database Professionals" www.RuleGen.com/pls/apex/f?p=14265:13<http://www.RuleGen.com/pls/apex/f?p=14265:13>

--

http://www.freelists.org/webpage/oracle-l Received on Thu May 07 2009 - 09:57:27 CDT