Re: Which sessions hold state on which packages

From: Yong Huang <yong321_at_yahoo.com>
Date: Fri, 19 Feb 2010 11:54:03 -0800 (PST)
Message-ID: <725917.83230.qm_at_web80601.mail.mud.yahoo.com>

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 - 13:54:03 CST