Re: Which sessions hold state on which packages

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Fri, 19 Feb 2010 12:37:45 -0800
Message-ID: <26fdee6e1002191237x2f447fc8u10f57f579bda6c27_at_mail.gmail.com>

Yong,
Your assessment of the question I am trying to ask is absolutely correct. Thanks for the clarification.

Unfortunately, I can not access the x$kgllk view, but that's a problem between me and our DBA's.
Regards,
Mike

On Fri, Feb 19, 2010 at 11:54 AM, Yong Huang <yong321_at_yahoo.com> wrote:

> Michael's question is about session state, but Kurt's answer is
> about who's executing the PL/SQL program unit. If a package is being
> executed, the package state is held. But if the state is held, the
> execution on the package may already have finished. You can test it
> this way.
>
> In session 1:
> create or replace package pkg as var number; end;
> /
> exec pkg.var := 1
>
> In session 2:
> create or replace package pkg /* xxx */ as var number; end;
> /
>
> In session 1:
> SQL> exec pkg.var := 1
> BEGIN pkg.var := 1; END;
>
> *
> ERROR at line 1:
> ORA-04068: existing state of packages has been discarded
>
> That indicates package state can be held after execution.
>
> Now, repeat the test, but just the session 1 part. Then run Kurt's
> query, which is based on x$kglob.kglhdpmd = 2. You won't find the
> session or package, because that query finds the package any session
> is executing.
>
> Tanel Poder's idea based on whether bitand(kgllkflg,256) is 256
> (for 10g) may work:
>
> http://www.freelists.org/post/oracle-l/How-to-determine-sessions-with-invalid-package-states,7
>
> Yong Huang
>
> ----- Original message -----
>
> SELECT o.inst_id inst_id,
> ...
> FROM x$kglob o,
> x$kglpn p,
> x$ksuse s
> WHERE o.inst_id = p.inst_id
> AND o.inst_id = s.inst_id
> AND o.kglhdpmd = 2
> AND p.kglpnhdl = o.kglhdadr
> AND s.addr = p.kglpnses
> AND o.kglobtyp != 0
> ;
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 19 2010 - 14:37:45 CST