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@xxxxxxxxx> 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: > > //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 > ; > > > >