Re: Which sessions hold state on which packages

On Thu, Feb 18, 2010 at 2:12 PM, Kurt Franke <Kurt-Franke_at_web.de> wrote:

> 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