oracle查看锁表和解锁 2020-09-01

查看是否被锁表：

SELECT
    a.object_name,
    b.session_id,
    c.serial#,
    c.program,
    c.username,
    c.command,
    c.machine,
    c.lockwait
FROM
    all_objects a,
    v$locked_object b,
    v$session c
WHERE
    a.object_id=b.object_id
AND c.sid=b.session_id;

查询锁表原因：

SELECT
    c.session_id sid,
    b.serial#,
    c.locked_mode,
    c.oracle_username,
    b.user#,
    c.os_user_name,
    b.machine,
    b.terminal,
    a.sql_text,
    a.action
FROM
    v$sqlarea a,
    v$session b,
    v$locked_object c
WHERE
    c.session_id = b.sid
AND b.prev_sql_addr = a.address
ORDER BY
    sid,
    b.serial#;

查看被锁的表：

SELECT
    p.spid,
    c.object_name,
    b.session_id,
    b.oracle_username,
    b.os_user_name
FROM
    v$process p,
    v$session a,
    v$locked_object b,
    all_objects c
WHERE
    p.addr=a.paddr
AND a.process=b.process
AND c.object_id=b.object_id;

解锁方法：

alter system kill session ’146′;    	–146为锁住的进程号，即spid