Re: How to determine sessions with invalid package states

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Thu, 7 May 2009 15:23:11 -0500
Message-ID: <203315c10905071323p429bc73dt8024df5641621a67_at_mail.gmail.com>

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_at_poderc.com> 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_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 - 15:23:11 CDT