RE: How to determine sessions with invalid package states
- From: "Tanel Poder" <tanel@xxxxxxxxxx>
- To: <rgravens@xxxxxxxxx>, "'oracle-l-freelists'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 7 May 2009 20:27:10 +0300
Hi,
You need to check which sessions have broken KGL locks against the objects
changed. X$KGLLK can help in this case.
Note that the kgllkflg = 256 means that kgl lock is broken - but only in
10g. In 9i you need to check for 1 instead of 256. And in 11g this check
works differently again..
SQL> select
2 sid,serial#,username,program
3 from
4 v$session
5 where
6 saddr in (select /*+ no_unnest */ kgllkuse
7 from x$kgllk
8 where
9 kglnahsh in (select /*+ no_unnest */ kglnahsh
10 from x$kglob
11 where
12 upper(kglnaown) like upper('&owner')
13 and upper(kglnaobj) like
upper('&object_name')
14 )
15 and bitand(kgllkflg,256)=256
16 )
17 /
Enter value for owner: SYS
Enter value for object_name: P
SID SERIAL# USERNAME PROGRAM
---------- ---------- ------------------------------
-----------------------------
146 326 SYS sqlplus.exe
SQL>
When I try to exec my package (which header I recompiled meanwhile) from
session 146 I get this:
SQL> exec p.proc;
BEGIN p.proc; END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SYS.P" has been invalidated
ORA-04065: not executed, altered or dropped package body "SYS.P"
ORA-06508: PL/SQL: could not find program unit being called: "SYS.P"
ORA-06512: at line 1
--
Regards,
Tanel Poder
http://blog.tanelpoder.com
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Rumpi Gravenstein
Sent: 07 May 2009 17:36
To: oracle-l-freelists
Subject: Re: How to determine sessions with invalid package states
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@xxxxxxxxx>
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
Other related posts:
