Oracle-锁与等待事件

以下是针对 Oracle 10g、11g 数据库锁相关查询的标准化整理，按功能分类并补充说明，便于实际运维中快速参考使用：

1. 查看被锁定的对象（表 / 索引等）

1.1. 查看所有被锁定的对象及锁定会话

SELECT 
  lo.session_id AS lock_sid, -- 锁定会话ID
  ao.owner AS obj_owner, -- 对象所属用户
  ao.object_name AS locked_obj, -- 被锁对象名
  ao.object_type AS obj_type, -- 对象类型（表、索引等）
  lo.locked_mode AS lock_mode -- 锁定模式（1-6，含义见下文说明）
FROM 
  v$locked_object lo
JOIN 
  dba_objects ao ON lo.object_id = ao.object_id
ORDER BY 
  lo.session_id, ao.object_name;

1.2. 按指定用户 / 表名筛选被锁定对象

SELECT 
  s.sid AS sess_id, -- 会话ID
  s.serial# AS ser#, -- 序列号
  l.type AS lock_type, -- 锁类型（TM=表级、TX=行级等）
  d.owner || '.' || d.object_name AS locked_obj -- 被锁对象名
FROM 
  v$session s
JOIN 
  v$lock l ON s.sid = l.sid
JOIN 
  dba_objects d ON d.object_id = l.id1 
  AND d.object_type = 'TABLE' -- 只查数据表
WHERE 
  d.owner = 'WXTZNC' -- 指定用户（大写）
  AND d.object_name = 'BD_SHIFT'; -- 指定表名（大写）

2. 查询锁定会话的详细信息

2.1. 锁定会话的用户 / 进程信息

SELECT 
  sess.sid AS sess_id, -- 会话ID
  sess.serial# AS ser#, -- 序列号
  lo.oracle_username AS db_user, -- 数据库用户名
  lo.os_user_name AS os_user, -- 操作系统用户
  ao.object_name AS locked_obj, -- 被锁对象名
  lo.locked_mode AS lock_mode, -- 锁定模式
  sess.machine AS client_machine, -- 客户端机器名
  sess.terminal AS terminal, -- 终端
  sess.logon_time AS login_time -- 登录时间
FROM 
  v$locked_object lo
JOIN 
  dba_objects ao ON lo.object_id = ao.object_id
JOIN 
  v$session sess ON lo.session_id = sess.sid
ORDER BY 
  sess.logon_time DESC;

2.2. 锁定会话对应的操作系统进程

SELECT 
  s.sid AS sess_id, -- 会话ID
  s.serial# AS ser#, -- 序列号
  s.username AS db_user, -- 数据库用户
  p.spid AS os_pid, -- 操作系统进程ID（可用于OS级杀进程）
  s.machine AS client_machine, -- 客户端机器
  s.program AS client_program -- 客户端程序（如PL/SQL Developer等）
FROM 
  v$session s
JOIN 
  v$process p ON s.paddr = p.addr
WHERE 
  s.sid IN (SELECT DISTINCT session_id FROM v$locked_object) -- 只查锁定会话
ORDER BY 
  s.sid;

3. 死锁检测与分析

3.1. 检查是否存在死锁及涉及的会话

SELECT 
  sess.sid AS sess_id, -- 会话ID
  sess.serial# AS ser#, -- 序列号
  lo.oracle_username AS lock_user, -- 锁定用户
  ao.object_name AS locked_obj, -- 被锁对象
  lo.locked_mode AS lock_mode, -- 锁定模式
  sess.blocking_session_status AS blk_status, -- 阻塞状态（'VALID'表示被阻塞）
  sess.blocking_session AS blk_sid -- 阻塞会话ID（持有锁的会话）
FROM 
  v$locked_object lo
JOIN 
  dba_objects ao ON lo.object_id = ao.object_id
JOIN 
  v$session sess ON lo.session_id = sess.sid
WHERE 
  sess.blocking_session IS NOT NULL; -- 只查被阻塞的会话

3.2. 死锁相关的 SQL 语句（锁定 / 被阻塞语句）

-- 被锁定会话执行的SQL
SELECT 
  s.sid AS sess_id, -- 会话ID
  s.sql_id AS sql_id, -- SQL唯一标识
  sa.sql_text AS locked_sql -- 锁定时执行的SQL
FROM 
  v$session s
JOIN 
  v$sqlarea sa ON s.sql_id = sa.sql_id
WHERE 
  s.sid IN (SELECT DISTINCT session_id FROM v$locked_object);

4. 锁阻塞关系查询（谁阻塞了谁）

4.1. 简洁版：阻塞与被阻塞会话关系

SELECT 
  s1.username || '@' || s1.machine || ' (SID=' || s1.sid || ')' AS blocked, -- 被阻塞方
  s2.username || '@' || s2.machine || ' (SID=' || s2.sid || ')' AS blocker, -- 阻塞方
  l1.type AS lock_type, -- 锁类型
  l1.id1 AS lock_id1, -- 锁标识1
  l1.id2 AS lock_id2 -- 锁标识2
FROM 
  v$lock l1
JOIN 
  v$session s1 ON l1.sid = s1.sid
JOIN 
  v$lock l2 ON l1.id1 = l2.id1 AND l1.id2 = l2.id2
JOIN 
  v$session s2 ON l2.sid = s2.sid
WHERE 
  l1.request > 0 -- 被阻塞的锁请求
  AND l2.lmode > 0 -- 持有锁的模式
  AND l1.block = 0 
  AND l2.block = 1;

 

4.2. 详细版：阻塞会话的等待事件与锁信息

SELECT 
  s1.sid AS blocked_sid, -- 被阻塞会话ID
  s1.serial# AS blocked_ser, -- 被阻塞序列号
  s1.username AS blocked_user, -- 被阻塞用户
  s1.event AS wait_event, -- 等待事件（如'enqueue'表示等待锁）
  s2.sid AS blocker_sid, -- 阻塞会话ID
  s2.serial# AS blocker_ser, -- 阻塞序列号
  s2.username AS blocker_user, -- 阻塞用户
  l1.type AS lock_type, -- 锁类型（TM=表级锁，TX=行级锁）
  l1.lmode AS hold_mode, -- 持有锁模式（见下文说明）
  l1.request AS req_mode -- 请求锁模式
FROM 
  v$lock l1
JOIN 
  v$session s1 ON l1.sid = s1.sid
JOIN 
  v$lock l2 ON l1.id1 = l2.id1 AND l1.id2 = l2.id2
JOIN 
  v$session s2 ON l2.sid = s2.sid
WHERE 
  l1.request > 0 -- 会话1请求锁
  AND l2.lmode > 0; -- 会话2持有锁

5. 终止锁定 / 阻塞会话

5.1. 单实例环境

ALTER SYSTEM KILL SESSION 'sid,serial#'; 
-- 示例：ALTER SYSTEM KILL SESSION '123,4567';

5.2. RAC 环境（指定实例）

ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id'; 
-- 示例：ALTER SYSTEM KILL SESSION '123,4567,@1'; -- @1表示实例1

6. 辅助查询工具

6.1. 查看会话等待事件（分析锁等待原因）

SELECT 
  sid AS sess_id, -- 会话ID
  event AS wait_event, -- 等待事件（如'enqueue'（锁等待）等）
  total_waits AS wait_cnt, -- 总等待次数
  time_waited AS wait_time -- 总等待时间(秒)
FROM 
  v$session_event
WHERE 
  sid IN (SELECT DISTINCT session_id FROM v$locked_object)
ORDER BY 
  time_waited DESC;

6.2. 查看长事务执行进度（判断是否因长事务导致锁等待）

SELECT 
  se.sid AS sess_id, -- 会话ID
  opname AS op_name, -- 操作名称（如'Table Scan'等）
  TRUNC(sofar / totalwork * 100, 2) || '%' AS progress, -- 进度
  elapsed_seconds AS elapsed_sec, -- 已执行时间(秒)
  ROUND(elapsed_seconds * (totalwork - sofar) / sofar) AS remain_sec, -- 剩余时间(秒)
  sql_text AS exec_sql -- 执行的SQL
FROM 
  v$session_longops sl
JOIN 
  v$sqlarea sa ON sl.sql_hash_value = sa.hash_value
JOIN 
  v$session se ON sl.sid = se.sid
WHERE 
  sofar != totalwork -- 未完成的操作
  AND sofar > 0
ORDER BY 
  start_time;

7 关键字段说明（同前，保持中文注释便于理解）

1. 锁定模式（lock_mode/hold_mode）：
   • 0：无锁
   • 1：空锁（NULL）
   • 2：行共享（RS）：允许并发查询，阻止独占锁
   • 3：行独占（RX）：允许修改行，阻止共享锁和独占锁
   • 4：共享锁（S）：允许并发查询，阻止修改
   • 5：共享行独占（SRX）：阻止多数修改操作
   • 6：独占锁（X）：完全阻止其他会话操作
2. 锁类型（lock_type）：
   • TM：表级锁（DML 操作自动获取）
   • TX：行级锁（事务修改行时获取）
   • AE：审计相关锁（通常可忽略）