RE: Which sessions hold state on which packages
- From: "Mathias Zarick" <Mathias.Zarick@xxxxxxxxxxxx>
- To: <Kurt-Franke@xxxxxx>, <oracle-l@xxxxxxxxxxxxx>, <michaeljmoore@xxxxxxxxx>
- Date: Fri, 19 Feb 2010 09:50:09 +0100
Hi Kurt, Michael,
interesting question and also I was happy to find an answer.
But unfortunately this did not work for me:
10.2.0.4
# Session 1
SQL> create package a
2 as
3 a char;
4 end;
5 /
Package created.
SQL> exec a.a:=1;
PL/SQL procedure successfully completed.
# Session 2
SQL> select * from v$code_object_in_use;
no rows selected
What I'm doing wrong?
Mathias
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Kurt Franke
Sent: Thursday, February 18, 2010 11:13 PM
To: oracle-l@xxxxxxxxxxxxx; michaeljmoore@xxxxxxxxx
Subject: 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
