Re: library cache pin wait

From: Kurt Franke <Kurt-Franke_at_web.de>
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-l
Received on Mon Oct 06 2008 - 16:13:25 CDT

Original text of this message