Re: Which sessions hold state on which packages

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@xxxxxxxxxxxxx
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

Other related posts: