一、问题背景
一次业务调整中，我们需要删除一个通过 DBLINK 访问远端数据库的视图：
VIEW_EMR_EXAMINEINFO_PACS
执行以下语句后，SQL*Plus 长时间没有返回：
DROP VIEW view_emr_examineinfo_pacs;
视图依赖 DBLINK，我们一开始先检查远端连接。随后查看会话等待信息，发现
DROP VIEW 正在等待
library cache lock，直接阻塞者是本地业务会话。
二、确认视图和 DBLINK 状态
先查询视图，确认远端数据库仍能访问：
select count(*)from view_emr_examineinfo_pacs;
结果正常返回：
COUNT(*) -------- 26443
当时可以确认：
-
DBLINK 可以建立连接，远端数据库也能响应；
-
视图查询可以执行，没有明显的网络中断。
继续执行：
desc view_emr_examineinfo_pacs;
字段信息也能返回。我们暂时排除 DBLINK 完全不可用的情况，转而检查本地会话的等待事件和阻塞关系。
三、检查数据库中的非空闲等待
当前环境使用 Oracle RAC。查询
gv$session 并保留
INST_ID，才能区分不同实例中的会话。
set linesize 250col event for a40 col program for a35 col machine for a25select inst_id, sid, serial#, username, status, event, wait_class, seconds_in_wait, blocking_instance, blocking_session, final_blocking_instance, final_blocking_session, sql_id, program, machinefrom gv$sessionwhere wait_class <> 'Idle'order by inst_id, sid;
查询结果中，执行
DROP VIEW 的 SQL*Plus 会话如下：
INST_ID 1 SID 2762 SERIAL# 27015 USERNAME HSP_GL STATUS ACTIVE EVENT library cache lock WAIT_CLASS Concurrency SECONDS_IN_WAIT 223 BLOCKING_INSTANCE 1 BLOCKING_SESSION 1333 PROGRAM sqlplus@node1 (TNS V1-V3) MACHINE node1
关键字段如下：
EVENT = library cache lock BLOCKING_INSTANCE = 1 BLOCKING_SESSION = 1333
DROP VIEW 正在等待 Oracle Library Cache 中的对象锁。我们根据
BLOCKING_INSTANCE 和
BLOCKING_SESSION 定位到实例 1 的 SID 1333，它是该会话的直接阻塞者。
四、
library cache lock 是什么
Oracle 用 Shared Pool 中的 Library Cache 保存 SQL、PL/SQL、对象定义和依赖关系等信息。
会话执行以下 DDL 时，需要获取目标对象的 Library Cache Lock：
DROP VIEWALTER VIEWDROP TABLEALTER TABLECREATE OR REPLACE ...
如果另一个会话在解析、编译或使用该对象，并持有不兼容的锁，DDL 会一直等到锁释放。本次阻塞过程可以简化为：
Session A 正在持有相关 Library Cache 资源 ↓ Session B DROP VIEW ↓ 申请 Library Cache Lock ↓ 无法立即获得 ↓ library cache lock ↓ DDL 长时间不返回
所以，下面的查询可以返回结果：
select count(*)from view_emr_examineinfo_pacs;
而下面的 DDL 仍可能等待：
drop view view_emr_examineinfo_pacs;
查询和 DDL 申请的锁不同。视图可查询，只能证明当时的查询链路可用，不能证明
DROP VIEW 能拿到所需的对象锁。
五、定位阻塞会话
直接阻塞者是：
INSTANCE 1 SID 1333
查询该会话的来源和状态：
set lines 250col username for a20 col event for a35 col program for a40 col machine for a30 col module for a30select inst_id, sid, serial#, username, status, event, wait_class, sql_id, prev_sql_id, program, machine, module, logon_timefrom gv$sessionwhere inst_id = 1 and sid = 1333;
得到：
INST_ID : 1 SID : 1333 SERIAL# : 52603 USERNAME : HIS_PACS STATUS : ACTIVE EVENT : single-task message WAIT_CLASS : Idle PROGRAM : JDBC Thin Client MACHINE : WIN-N5IKG2PRCUV MODULE : JDBC Thin Client
这是一个来自业务服务器的 JDBC 会话：
HIS_PACS JDBC Thin Client WIN-N5IKG2PRCUV
它的
EVENT 为：
single-task message
再查看它当前和上一条 SQL：
select inst_id, sql_id, sql_textfrom gv$sqlwhere inst_id = 1 and sql_id in ( select sql_id from gv$session where inst_id = 1 and sid = 1333 union select prev_sql_id from gv$session where inst_id = 1 and sid = 1333 );
结果中有一条递归 SQL：
select value$from props$where name = 'GLOBAL_DB_NAME';
视图通过 DBLINK 访问远端库，这条递归 SQL 可能与对象解析、远端连接或依赖处理有关。仅凭该 SQL 还不能确认它如何取得锁；现场能够确认的是
gv$session 给出的直接阻塞关系：
DROP VIEW Session ↓ library cache lock ↓ BLOCKING_SESSION = 1333
六、确认影响范围
除执行
DROP VIEW 的 SID 2762 外，一个 Oracle 后台会话也被 SID 1333 阻塞：
INST_ID 1 SID 2386 USERNAME SYS PROGRAM oracle@node1 (J000) EVENT library cache lock BLOCKING_SESSION 1333
SID 1333 已经影响手工 DDL 和 J000 后台会话。阻塞关系如下：
┌── SID 2762 │ DROP VIEW │ library cache lock SID 1333 ───────────┤ │ └── SID 2386 J000 library cache lock
两个等待会话都将 SID 1333 列为 blocker，可以把它作为现场的阻塞源继续处理。
七、处理阻塞会话
业务方确认 SID 1333 可以中断后，在 RAC 环境执行：
alter system kill session '1333,52603,@1' immediate;
RAC 环境中，目标会话由以下三项共同确定：
SID SERIAL# INST_ID
Kill Session 时应带上实例号：
alter system kill session 'SID,SERIAL#,@INST_ID' immediate;
不同实例可能出现相同 SID，省略
@INST_ID 会增加误操作风险。
中断阻塞会话后，再次执行：
DROP VIEW view_emr_examineinfo_pacs;
成功返回：
View dropped.
视图删除成功。
八、故障链路
VIEW_EMR_EXAMINEINFO_PACS │ │ 通过 DBLINK 查询远端数据库 ↓ SELECT COUNT(*) 正常 DESC 正常 │ ↓ DBLINK 基本正常 │ ↓ 执行 DROP VIEW │ ↓ 长时间没有返回 │ ↓ 检查 GV$SESSION │ ↓ EVENT = library cache lock │ ↓ BLOCKING_INSTANCE = 1 BLOCKING_SESSION = 1333 │ ↓ 定位 1:1333 HIS_PACS / JDBC Thin Client │ ↓ 确认可以中断 │ ↓ KILL SESSION │ ↓ DROP VIEW 成功
九、排查时容易误判的地方
1. 把 DBLINK 当作唯一嫌疑
视图依赖 DBLINK，不代表故障一定出在远端连接。
如果：
select count(*)from view_emr_examineinfo_pacs;
能够返回时，可以把排查重点转向本地等待事件。DDL 长时间无响应时，重点关注：
library cache lock library cache pin cursor: pin S wait on X enq: DD - contention
等等待事件。
2. 用查询结果判断 DDL 状态
SELECT 和 DDL 对对象锁的要求不同。
例如：
SELECT * FROM VIEW;
能够执行，不能证明：
DROP VIEW VIEW;
可以取得所需的 Library Cache Lock。视图查询和视图删除需要分开验证。
3. RAC 环境只记录 SID
本次现场中，不同实例出现了相同 SID。RAC 排查应使用：
select inst_id, sid, serial#, ...from gv$session;
只记录下面的信息无法唯一定位会话：
SID = 2386
应同时记录：
INST_ID + SID + SERIAL#
执行 Kill 时也要指定实例：
alter system kill session 'SID,SERIAL#,@INST_ID' immediate;
4. 批量中断等待会话
现场还有其他会话处于以下等待：
library cache lock cursor: pin S wait on X
这些会话可能是受害者，不能据此批量中断。应从目标 DDL 会话出发，根据：
BLOCKING_INSTANCE BLOCKING_SESSION FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION
沿阻塞链定位源头。确认会话归属、业务影响和可中断性后，再处理阻塞者。
十、通用排查 SQL
下面这些 DDL 长时间不返回时，可以先检查非空闲等待：
-
DROP TABLE 卡住；
-
DROP VIEW 卡住；
-
ALTER TABLE 卡住；
-
CREATE OR REPLACE 卡住；
-
存储过程编译卡住；
-
DDL 长时间不返回；
select inst_id, sid, serial#, username, status, event, wait_class, seconds_in_wait, blocking_instance, blocking_session, final_blocking_instance, final_blocking_session, sql_id, program, machinefrom gv$sessionwhere wait_class <> 'Idle'order by inst_id, sid;
如果等待事件为：
library cache lock
且以下字段有值：
BLOCKING_INSTANCE BLOCKING_SESSION
根据实例号和 SID 查询阻塞会话：
select inst_id, sid, serial#, username, status, event, sql_id, prev_sql_id, program, machine, module, logon_timefrom gv$sessionwhere inst_id = <blocking_instance> and sid = <blocking_session>;
确认会话来源、业务影响和可中断性后，再决定是否执行：
alter system kill session 'SID,SERIAL#,@INST_ID' immediate;
十一、结论
本次现场中，DBLINK 查询和
DESC 均可返回。我们从
gv$session 看到
DROP VIEW 正在等待
library cache lock，直接阻塞者为实例 1 的 SID 1333。该业务 JDBC 会话还阻塞了一个 J000 后台会话。
排查此类问题时，重点查看
gv$session 中的：
EVENT BLOCKING_INSTANCE BLOCKING_SESSION FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION
本次定位结果为：
Instance 1 / SID 1333
业务方确认可以中断该会话后，执行 Kill Session。随后：
DROP VIEW view_emr_examineinfo_pacs;
成功返回。处理同类故障时，先确认等待事件和阻塞链，再核实阻塞会话能否中断。不要因 SQL 长时间未返回就重复执行 DDL 或重启数据库。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/23825935/viewspace-3180745/ ，如需转载，请注明出处，否则将追究法律责任。
