Oracle锁表查询以及解锁

在 Oracle 数据库中，处理锁表问题通常需要以下步骤：查询锁表信息和执行解锁操作。以下是详细说明：

---

一、查询锁表语句

1. 基本锁表查询（常用）

SELECT 
    a.sid, 
    a.serial#, 
    a.username, 
    a.osuser, 
    a.machine, 
    a.program, 
    b.object_id, 
    c.object_name, 
    b.locked_mode,
    a.status
FROM 
    v$session a,
    v$locked_object b,
    dba_objects c
WHERE 
    a.sid = b.session_id
    AND b.object_id = c.object_id;

• 字段说明：
  • sid 和 serial#：会话的唯一标识符（用于解锁）。
  • username：数据库用户名。
  • object_name：被锁的表名。
  • locked_mode：锁模式（2=行级共享锁，3=行级排他锁，6=表级排他锁等）。

2. 查询锁的阻塞关系（定位源头）

SELECT 
    l1.sid AS blocking_sid,
    l2.sid AS blocked_sid,
    s1.username AS blocking_user,
    s2.username AS blocked_user,
    s1.machine AS blocking_machine,
    s2.machine AS blocked_machine,
    s1.sql_id AS blocking_sql,
    s2.sql_id AS blocked_sql
FROM 
    v$lock l1,
    v$lock l2,
    v$session s1,
    v$session s2
WHERE 
    l1.block = 1
    AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l1.id2 = l2.id2
    AND l1.sid = s1.sid
    AND l2.sid = s2.sid;

3. 快速查看被锁对象

SELECT * FROM v$locked_object;

---

二、解锁操作

1. 提交或回滚事务（推荐）

找到持有锁的会话后，联系用户提交 (COMMIT) 或回滚 (ROLLBACK) 事务，锁会自动释放。

2. 强制终止会话（谨慎使用）

如果无法联系用户，使用以下命令终止会话：

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

• 示例：

  ALTER SYSTEM KILL SESSION '25,12345' IMMEDIATE;
  

3. 批量终止锁表会话

生成终止命令的脚本：

SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS kill_cmd
FROM v$session
WHERE sid IN (SELECT session_id FROM v$locked_object);

---

三、注意事项

1. 权限要求：

   • 执行上述操作需要 DBA 权限或 ALTER SYSTEM 权限。
   • 查询 dba_objects 需要 SELECT_CATALOG_ROLE 权限。

2. 锁模式说明：

   • 0：无锁
   • 1：NULL（空）
   • 2：行级共享锁（Row-S）
   • 3：行级排他锁（Row-X）
   • 4：共享表锁（Share）
   • 5：共享行级排他锁（S/Row-X）
   • 6：排他表锁（Exclusive）

3. 风险提示：

   • 强制终止会话可能导致事务未提交的数据丢失。
   • 生产环境谨慎操作，建议优先联系相关用户。

---

四、扩展场景

1. 查询长时间未提交的事务

SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.status,
    t.start_time,
    t.used_ublk,
    t.log_io,
    t.phy_io
FROM 
    v$session s,
    v$transaction t
WHERE 
    s.taddr = t.addr;

2. 死锁处理

Oracle 会自动检测死锁并回滚其中一个事务。手动处理步骤：

1. 查询死锁信息：

   SELECT * FROM v$deadlock_history;
   

2. 终止相关会话（方法同上）。

---

通过以上步骤，可以快速定位和解决 Oracle 中的锁表问题。