Tanel It is interesting you are using kglnahsh as a join key between x$kgllk and x$kglob. Is there any reason for that? I usually use x$kglob.kglhdadr = x$kgllk.kgllkhdl.. Cheers Riyaj Shamsudeen Principal DBA, Ora!nternals - http://www.orainternals.com Specialists in Performance, Recovery and EBS11i Blog: http://orainternals.wordpress.com On Thu, May 7, 2009 at 12:27 PM, Tanel Poder <tanel@xxxxxxxxxx> wrote: > 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 > >