Library cache pins are used to manage library cache concurrency. Pinning an object causes the heaps to be loaded into memory (if not already loaded). PINS can be acquired in NULL, SHARE or EXCLUSIVE modes and can be considered like a special form of lock. A wait for a “library cache pin” implies some other session holds that PIN in an incompatible mode.
The following SQL can be used to show the sessions which are holding and/or requesting pins on the object that given in P1 (Column P1 of v$session_wait) in the wait:
SELECT s.sid, kglpnmod "Mode", kglpnreq "Req" FROM x$kglpn p, v$session s WHERE p.kglpnuse=s.saddr AND kglpnhdl='&P1RAW' ;
Note that an X request (3) will be blocked by any pins held S mode (2) on the object and an S request (2) will be blocked by any X mode (3) pin held, or may queue behind some other X request.