RE: How to determine sessions with invalid package states

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: