今天论坛中发现一个问题在进行编译或者删除存储过程的时候一直卡住，
当然这个很可能是LIBRARY CACHE PIN引起的。概念如下：
An Oracle instance has a library cache that contains the description of
different types of objects e.g. cursors, indexes, tables, views, procedures,
... Those objects cannot be changed when they are used. They are locked by a
mechanism based on library locks and pins. A session that need to use an object
will first acquire a library lock in a certain mode (null, shared or exclusive)
on the object, in order to prevent other sessions from accessing the same
object (e.g. exclusive lock when recompiling a package or view) or to maintain
the object definition for a long time. Locking an object is sometimes referred
as the job to locate it in the library cache and lock it in a certain mode.
If the session wants to modify or examine the object, it must acquire after
the lock also a pin in a certain mode (again null, shared or exclusive).
通过语句
select distinct decode(lob.kglobtyp,
0,
'NEXT OBJECT',
1,
'INDEX',
2,
'TABLE',
3,
'CLUSTER',
4,
'VIEW',
5,
'SYNONYM',
6,
'SEQUENCE',
7,
'PROCEDURE',
8,
'FUNCTION',
9,
'PACKAGE',
11,
'PACKAGE BODY',
12,
'TRIGGER',
13,
'TYPE',
14,
'TYPE BODY',
19,
'TABLE PARTITION',
20,
'INDEX PARTITION',
21,
'LOB',
22,
'LIBRARY',
23,
'DIRECTORY',
24,
'QUEUE',
28,
'JAVA SOURCE',
29,
'JAVA CLASS',
30,
'JAVA RESOURCE',
32,
'INDEXTYPE',
33,
'OPERATOR',
34,
'TABLE SUBPARTITION',
35,
'INDEX SUBPARTITION',
40,
'LOB PARTITION',
41,
'LOB SUBPARTITION',
42,
'MATERIALIZED VIEW',
43,
'DIMENSION',
44,
'CONTEXT',
46,
'RULE SET',
47,
'RESOURCE PLAN',
48,
'CONSUMER GROUP',
51,
'SUBSCRIPTION',
52,
'LOCATION',
55,
'XML SCHEMA',
56,
'JAVA DATA',
57,
'SECURITY PROFILE',
59,
'RULE',
62,
'EVALUATION CONTEXT',
'UNDEFINED') object_type,
lob.KGLNAOBJ object_name,
pn.KGLPNMOD lock_mode_held,
pn.KGLPNREQ lock_mode_requested,
ses.sid,
ses.serial#,
ses.username,
ses.process,
vp.spid
FROM x$kglpn pn,
v$session ses,
x$kglob lob,
v$session_wait vsw,
v$process vp
WHERE pn.KGLPNUSE = ses.saddr
and pn.KGLPNHDL = lob.KGLHDADR
and lob.kglhdadr = vsw.p1raw
and ses.PADDR = vp.ADDR
and vsw.event = 'library cache pin'
order by lock_mode_held desc;
查看PIN的对象，确实有一个阻塞源。原来是在跑JOB，进一步分析发现了
SQL*Net more data from dblink
这个应该是网络问题，在INSERT到远程库的时候引起的，由于在JOB中有这个过程，所以JOB迟迟不能完成，同时存储过程也不能完成。
下面是METALINK的原文
Insert Into Remote Table Using DBLINK Over VPN Tunnel Hangs on Large Number of Rows [ID 742535.1]
--------------------------------------------------------------------------------
修改时间 19-MAR-2010 类型 PROBLEM 状态 PUBLISHED
In this Document
Symptoms
Changes
Cause
Solution
References
--------------------------------------------------------------------------------
Applies to:
Oracle Net Services - Version: 9.2.0.1.0 to 11.1.0.7.0
Information in this document applies to any platform.
Checked for relevance on 19-MAR-2010.
Symptoms
Suddenly an INSERT (as) SELECT FROM DBLINK hangs with source database waiting on "SQL*Net more data from dblink" wait event and the remote (target) shadow process disappearing.
SQL> INSERT INTO TABLEX (SELECT SESSION_ID, OBS_DT, UNIT_ID FROM
WEB_SUMM WHERE ROWNUM 280 rows created.
SQL> INSERT INTO TABLEX (SELECT SESSION_ID, OBS_DT, UNIT_ID FROM
WEB_SUMM WHERE ROWNUM This hangs indefinitely with source in "SQL*Net more data from dblink" wait event.
Similarly, create table as select over the DBLINK also hangs:
SQL> CREATE TABLE TEST1 AS (SELECT SESSION_ID, OBS_DT, UNIT_ID FROM
WEB_SUMM WHERE ROWNUM
Table created.
---------------
SQL> CREATE TABLE TEST1 AS (SELECT SESSION_ID, OBS_DT, UNIT_ID FROM
WEB_SUMM WHERE ROWNUM
-- This also hangs indefinitely with source in "SQL*Net more data from dblink" wait event.
A PL/SQL doing an explicit cursor just fetching (not inserting) fails after 20 or so rows.
The only thing that runs to completion is plain SELECT from Sqlplus.
Changes
Database Links to databases on Local Area Network (LAN) do not exhibit this problem. This issue is limited to a database link where the target is a remote database accessed via a VPN Tunnel using default port 1521.
Cause
Problem was isolated to use of port 1521 over a VPN Tunnel that utilizes Cisco 5400/5500 Series
Adaptive Security Appliances (ASA) where the Cisco SQLnet fixup protocol/Sql Inspector was
enabled. However, on port 1522 where this Sql packet Inspection was not enabled, the problem did
not reproduce. Note: The Cisco 5400/5500 Series Adaptive Security Appliances (ASA) have this SQLnet fixup protocol/Sql Inspection enabled by default for port 1521.