RE: How to determine sessions with invalid package states

From: Tanel Poder <tanel_at_poderc.com>
Date: Thu, 7 May 2009 20:27:10 +0300
Message-ID: <8D5E5D1CA0474032B03B6267F926F955_at_porgand>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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_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

--

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