Re: Which sessions hold state on which packages

From: Kurt Franke <Kurt-Franke_at_web.de>
Date: Thu, 18 Feb 2010 23:12:58 +0100 (CET)
Message-ID: <13469455.238848.1266531178273.JavaMail.fmail_at_mwmweb033>

Micheal,

the following view(s) will list this.
This view can only be created in SYS schema because there must be access to some X$ Tables. (maybe some people want to avoid this, but it is the only methode to access this information without login to SYS for every check)

regards

kf

---

 

CREATE OR REPLACE VIEW gv_$code_object_in_use AS
SELECT o.inst_id inst_id,

       decode(o.kglobtyp,
              5, 'SYNONYM',
              7, 'PROCEDURE',
              8, 'FUNCTION',
              9, 'PACKAGE',
              11, 'PACKAGE BODY',
              12, 'TRIGGER',
              13, 'TYPE',
              14, 'TYPE BODY',
              22, 'LIBRARY',
              23, 'DIRECTORY',
              28, 'JAVA SOURCE',
              29, 'JAVA CLASS',
              30, 'JAVA RESOURCE',
              31, 'JAVA JAR',
              33, 'OPERATOR',
              53, 'REMOTE OBJECT',
              55, 'XML SCHEMA',
              56, 'JAVA SHARED DATA',
              'OTHER') type,
       o.kglobtyp type#,
       substr(o.kglnaown,1,30) owner,
       substr(o.kglnaobj,1,30) object_name,
       s.indx sid,
       s.ksuseser serial#,
       s.ksuudlna username
 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
;

CREATE PUBLIC SYNONYM gv$code_object_in_use FOR sys.gv_$code_object_in_use;

CREATE OR REPLACE VIEW v_$code_object_in_use AS
SELECT type, type#, owner, object_name, sid, serial#, username  FROM gv_$code_object_in_use
 WHERE inst_id = userenv('Instance')
;

CREATE PUBLIC SYNONYM v$code_object_in_use FOR sys.v_$code_object_in_use;   

---

---

Von: Michael Moore
Gesendet: 18.02.2010 22:27:01
An: oracle-l_at_freelists.org
Betreff: Which sessions hold state on which packages

Is there a view or query I can use to show which sessions hold state on which packages? tia

Mike

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

Received on Thu Feb 18 2010 - 16:12:58 CST