Re: library cache pin wait
Date: Mon, 06 Oct 2008 23:13:25 +0200
Message-Id: <1091475080@web.de>
hi,
> > 2. I check v$sess_io and no IO is incrementing for this session.
>
> Why not check V$ACCESS for this object? That should show you the other
> sessions that are pinning this object...
v$access doesn't list the pinned objects.
it lists objects where currently active code objects are depending on.
this is a superset of the actually pinned code objects because they pinned when the first call on it is done, not when a depending object is called.
to get the really pinned objects x$kglpn must be used - no v$-view is there for this to oracle 10.2, for oracle 11 I don't have the information.
but its simple to build an own v$-view for pinned objects - the code is following
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;
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 06 2008 - 16:13:25 CDT