Re: Which sessions hold state on which packages
- From: Yong Huang <yong321@xxxxxxxxx>
- To: Kurt-Franke@xxxxxx, michaeljmoore@xxxxxxxxx
- Date: Fri, 19 Feb 2010 11:54:03 -0800 (PST)
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:
//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
;
--
//www.freelists.org/webpage/oracle-l
Other related posts: