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;

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