Oracle数据库锁表查询与解锁操作

1. 查询锁表信息

1.1 基本锁表查询语句

-- 查询当前所有锁表信息
SELECT 
    l.session_id AS sid,
    s.serial#,
    l.locked_mode,
    l.oracle_username,
    l.os_user_name,
    s.machine,
    s.terminal,
    o.object_name,
    o.object_type,
    s.logon_time
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, serial#;

1.2 详细的锁表信息查询

-- 详细的锁表信息，包含SQL语句
SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.osuser,
    s.machine,
    s.program,
    s.status,
    l.type lock_type,
    l.lmode,
    l.request,
    l.ctime,
    o.object_name,
    o.object_type,
    q.sql_text
FROM 
    v$session s,
    v$lock l,
    dba_objects o,
    v$sql q
WHERE 
    s.sid = l.sid
    AND l.id1 = o.object_id(+)
    AND s.sql_address = q.address(+)
    AND s.sql_hash_value = q.hash_value(+)
    AND l.type = 'TM'
ORDER BY 
    s.sid, l.ctime DESC;

1.3 查询阻塞会话信息

-- 查找阻塞其他会话的锁
SELECT 
    s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' 
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
FROM 
    v$lock l1,
    v$session s1,
    v$lock l2,
    v$session s2
WHERE 
    s1.sid = l1.sid
    AND s2.sid = l2.sid
    AND l1.block = 1
    AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l1.id2 = l2.id2;

1.4 查询具体的锁等待关系

-- 锁等待关系链
SELECT 
    LPAD(' ', (LEVEL-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
    s.sid,
    s.serial#,
    s.lockwait,
    s.status,
    s.machine,
    s.program,
    s.sql_id
FROM 
    v$session s
START WITH 
    s.sid IN (SELECT sid FROM v$lock WHERE block = 1)
CONNECT BY 
    PRIOR s.sid = s.blocking_session;

2. 解锁操作

2.1 手动解锁单个会话

-- 根据SID和SERIAL#解锁
ALTER SYSTEM KILL SESSION 'sid,serial#';

-- 示例
ALTER SYSTEM KILL SESSION '123,4567';

2.2 强制立即解锁

-- 立即终止会话（强制方式）
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 示例
ALTER SYSTEM KILL SESSION '123,4567' IMMEDIATE;

3. 批量解锁脚本

3.1 生成批量解锁语句脚本

-- 生成解锁所有锁表会话的语句
SELECT 
    'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' AS kill_command,
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.machine,
    s.program,
    o.object_name
FROM 
    v$session s,
    v$locked_object l,
    dba_objects o
WHERE 
    s.sid = l.session_id
    AND l.object_id = o.object_id
    AND s.username NOT IN ('SYS', 'SYSTEM')  -- 排除系统用户
ORDER BY 
    s.logon_time;

3.2 根据条件生成批量解锁语句

-- 根据特定条件生成解锁语句（按用户、机器名等）
SELECT 
    'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''';' AS kill_command,
    sid,
    serial#,
    username,
    machine,
    program
FROM 
    v$session
WHERE 
    sid IN (
        SELECT session_id 
        FROM v$locked_object
    )
    -- 添加过滤条件
    AND username = '特定用户名'           -- 按用户名过滤
    AND machine LIKE '%特定机器名%'      -- 按机器名过滤
    AND program LIKE '%特定程序%'        -- 按程序名过滤
    AND status = 'INACTIVE';             -- 只杀非活动会话

3.3 批量解锁存储过程

-- 创建批量解锁存储过程
CREATE OR REPLACE PROCEDURE kill_locked_sessions(
    p_username IN VARCHAR2 DEFAULT NULL,
    p_machine IN VARCHAR2 DEFAULT NULL
) AS
    CURSOR lock_cursor IS
        SELECT s.sid, s.serial#
        FROM v$session s, v$locked_object l
        WHERE s.sid = l.session_id
        AND (p_username IS NULL OR s.username = p_username)
        AND (p_machine IS NULL OR s.machine LIKE '%' || p_machine || '%')
        AND s.username NOT IN ('SYS', 'SYSTEM');
        
    v_count NUMBER := 0;
BEGIN
    FOR lock_rec IN lock_cursor LOOP
        BEGIN
            EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || 
                             lock_rec.sid || ',' || lock_rec.serial# || '''';
            v_count := v_count + 1;
            DBMS_OUTPUT.PUT_LINE('已终止会话: SID=' || lock_rec.sid || 
                                ', SERIAL#=' || lock_rec.serial#);
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('终止会话失败: SID=' || lock_rec.sid || 
                                    ', 错误: ' || SQLERRM);
        END;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('共终止 ' || v_count || ' 个会话');
END kill_locked_sessions;
/

-- 使用存储过程示例
-- EXEC kill_locked_sessions('USERNAME', 'MACHINE_NAME');
-- EXEC kill_locked_sessions();  -- 终止所有非系统锁表会话

4. 预防锁表的监控脚本

4.1 实时监控锁表情况

-- 创建锁表监控视图
CREATE OR REPLACE VIEW lock_monitor_view AS
SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.program,
    s.status,
    l.type lock_type,
    DECODE(l.lmode,
        0, 'None',
        1, 'Null',
        2, 'Row-S (SS)',
        3, 'Row-X (SX)',
        4, 'Share',
        5, 'S/Row-X (SSX)',
        6, 'Exclusive',
        TO_CHAR(l.lmode)
    ) lock_mode,
    o.object_name,
    o.object_type,
    s.sql_id,
    s.logon_time
FROM 
    v$session s,
    v$lock l,
    dba_objects o
WHERE 
    s.sid = l.sid
    AND l.id1 = o.object_id(+)
    AND l.type = 'TM'
ORDER BY 
    s.logon_time DESC;

4.2 定期检查长时间锁表

-- 查询长时间持有的锁
SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.program,
    l.ctime lock_hold_seconds,
    o.object_name,
    s.sql_id
FROM 
    v$session s,
    v$lock l,
    dba_objects o
WHERE 
    s.sid = l.sid
    AND l.id1 = o.object_id(+)
    AND l.type = 'TM'
    AND l.ctime > 300  -- 锁持有时间超过5分钟
ORDER BY 
    l.ctime DESC;

5. 注意事项

1. 谨慎操作：解锁操作会终止用户会话，可能导致数据丢失或事务回滚
2. 权限要求：需要具有 ALTER SYSTEM 权限
3. 系统会话：不要随意终止SYS、SYSTEM等系统用户的会话
4. 备份确认：在执行批量解锁前，建议先确认生成的语句
5. 业务影响：评估解锁操作对业务的影响，选择合适的时间窗口

6. 常用查询组合

-- 一键查询所有相关信息
SELECT 
    '-- SID: ' || s.sid || ', SERIAL: ' || s.serial# || ', User: ' || s.username AS session_info,
    '-- Object: ' || o.object_name || ', Type: ' || o.object_type AS object_info,
    '-- Machine: ' || s.machine || ', Program: ' || s.program AS client_info,
    '-- SQL: ' || SUBSTR(q.sql_text, 1, 100) AS sql_preview,
    'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE; -- ' || 
    s.username || ' on ' || s.machine AS kill_command
FROM 
    v$session s,
    v$locked_object l,
    dba_objects o,
    v$sql q
WHERE 
    s.sid = l.session_id
    AND l.object_id = o.object_id
    AND s.sql_address = q.address(+)
    AND s.sql_hash_value = q.hash_value(+)
ORDER BY 
    s.username, o.object_name;