Oracle查询表锁情况的几种方式

1. 使用V$LOCK和V$SESSION视图

SELECT l.session_id sid,
       s.serial#,
       l.locked_mode,
       l.oracle_username,
       l.os_user_name,
       s.machine,
       s.terminal,
       o.object_name,
       o.object_type
FROM v$locked_object l,
     dba_objects o,
     v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial#;

2. 使用DBA_BLOCKERS和DBA_WAITERS视图

-- 查询阻塞会话
SELECT * FROM dba_blockers;

-- 查询等待会话
SELECT * FROM dba_waiters;

3. 使用V$SESSION_WAIT视图

SELECT sw.sid,
       sw.event,
       sw.wait_class,
       sw.seconds_in_wait,
       sw.state,
       s.username,
       s.status,
       s.machine,
       s.program
FROM v$session_wait sw,
     v$session s
WHERE sw.sid = s.sid
AND sw.wait_class != 'Idle'
ORDER BY sw.seconds_in_wait DESC;

4. 使用V$LOCKED_OBJECT视图

SELECT lo.session_id,
       lo.oracle_username,
       lo.os_user_name,
       do.object_name,
       do.object_type,
       lo.locked_mode
FROM v$locked_object lo,
     dba_objects do
WHERE lo.object_id = do.object_id;

5. 使用DBA_DML_LOCKS视图

SELECT * FROM dba_dml_locks;

6. 使用DBA_LOCK_INTERNAL视图

SELECT * FROM dba_lock_internal;

7. 使用V$TRANSACTION和V$SESSION视图

SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       t.start_time,
       t.used_ublk,
       t.used_urec
FROM v$transaction t,
     v$session s
WHERE t.ses_addr = s.saddr;

8. 使用AWR报告

如果需要分析历史锁情况，可以生成AWR报告：

-- 生成AWR报告
SELECT * FROM TABLE(dbms_workload_repository.awr_report_html(
    l_dbid => (SELECT dbid FROM v$database),
    l_inst_num => (SELECT instance_number FROM v$instance),
    l_bid => <begin_snap_id>,
    l_eid => <end_snap_id>
));

解锁方法

找到锁定的会话后，可以使用以下命令终止会话：

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

注意：在生产环境中终止会话前，请确保了解其影响。