Oracle查锁表（史上最全）

Oracle查锁表

Oracle分两种锁，一种是DDL锁，一种是DML锁。

一、Oracle DDL锁的解锁（dba_ddl_locks视图）

1.1、查表的DDL锁的详情（kill session脚本、表名、执行锁表的SQL等）

查DDL锁的数据字典，SQL如下：

SELECTDISTINCT'alter system kill session '''|| s.sid ||','|| s.serial# || ',@' ||
                s.inst_id ||''' immediate;'AS kill_session_scripts
               ,s.sql_id
               ,a.sql_text
               ,s.sid
               ,s.serial#FROM dba_ddl_locks l
      ,gv$session    s
      ,gv$sqlarea     a
 WHERE1=1AND l.session_id = s.sid
   AND s.sql_id = a.sql_id
   AND lower(a.sql_text)NOTLIKE'%alter system kill session %'-- AND l.owner IN ('TZQ','LOG');

查表的DDL锁的详情的查询结果如下图所示：

1.2、解锁表的DDL锁

有两种方式可以解锁表的DDL锁。

• 一是：执行kill session脚本。
• 二是：调用tzq_server_pkg包的kill_session存过执行杀会话kill session。

1.2.1、解锁表的DDL锁 - 1、执行kill session脚本

Ⅰ、打开命令窗口

Ⅱ、执行上面生成好的kill session脚本

alter system killsession'314,93,@1' immediate;

1.2.2、解锁表的DDL锁 - 2、调用tzq_server_pkg包的kill_session存过执行杀会话kill session。

tzq_server_pkg包的代码详见博客：Oracle解锁表、包、用户、杀会话、停job
打开命令行窗口，执行下面命令：

set serveroutput onexecute sys.tzq_server_pkg.kill_session(6335,15519);

二、Oracle DML锁的解锁（gv$locked_object视图）

2.1、查表的DML锁的详情（kill session脚本、表名、执行锁表的SQL等）

查DML锁的数据字典，SQL如下：

SELECTDISTINCT'alter system kill session '''|| s.sid ||','|| s.serial# || ',@' ||
                s.inst_id ||''' immediate;'AS kill_session_scripts
               ,o.owner
               ,o.object_name
               ,s.sql_id
               ,a.sql_text
               ,s.sid
               ,s.serial#FROM gv$locked_object l
      ,dba_objects      o
      ,gv$session       s
      ,gv$sqlarea        a
 WHERE l.object_id = o.object_id
   AND l.session_id = s.sid
   AND l.inst_id = s.inst_id
   AND s.sql_id = a.sql_id
   -- AND o.owner IN ('TZQ','LOG');

查表的DML锁的详情的查询结果如下图所示：

2.2、解锁表的DML锁

有两种方式可以解锁表的DML锁。

• 一是：执行kill session脚本。
• 二是：调用tzq_server_pkg包的kill_session存过执行杀会话kill session。

2.2.1、解锁表的DML锁 - 1、执行kill session脚本

Ⅰ、打开命令窗口

Ⅱ、执行上面生成好的kill session脚本

alter system killsession'314,93,@1' immediate;

2.2.2、解锁表的DML锁 - 2、调用tzq_server_pkg包的kill_session存过执行杀会话kill session。

tzq_server_pkg包的代码详见博客：Oracle解锁表、包、用户、杀会话、停job
打开命令行窗口，执行下面命令：

set serveroutput onexecute sys.tzq_server_pkg.kill_session(6335,15519);

三、附录

3.1、根据sid查sql_text（gv$session、gv$sqlarea）

SELECT s.sid
      ,s.serial#,s.sql_id
      ,s.sql_hash_value
      ,s.username
      ,a.sql_text
  FROM gv$session s
  LEFTJOIN gv$sqlarea a
    ON s.sql_id = a.sql_id
 WHERE s.sql_id ISNOTNULLAND a.sql_text NOTLIKE'%AND a.sql_text NOT LIKE %';

3.2、查锁表的详情（dba_locks视图）

SELECTDISTINCT'alter system kill session '''|| s.sid ||','|| s.serial# || ',@' ||
                s.inst_id ||''' immediate;'AS kill_session_scripts
      ,l.session_id
      ,s.serial#,l.lock_TYPE
      ,l.mode_held
      ,l.mode_requested
      ,CASEWHEN o1.object_name ISNOTNULLTHEN o1.owner||'.'||o1.object_name
         ELSENULLENDAS id1_object_name
      ,CASEWHEN o2.object_name ISNOTNULLTHEN o2.owner||'.'||o2.object_name
         ELSENULLENDAS id2_object_name
      ,l.last_convert
      ,l.blocking_others
      ,a.SQL_TEXT
  FROM dba_locks l
  LEFTJOIN dba_objects o1
    ON l.lock_id1 = o1.OBJECT_ID
  LEFTJOIN dba_objects o2
    ON l.lock_id2 = o2.OBJECT_ID
  LEFTJOIN gv$session s
    ON l.session_id = s.SID
  LEFTJOIN v$sqlarea a
    ON s.sql_id = a.sql_id
 WHERE1=1AND a.SQL_TEXT ISNOTNULLAND(o1.owner IN('TZQ','LOG')OR
        o2.owner IN('TZQ','LOG'));

3.3、Oracle查询锁定表的会话信息（gv$session、gv$process、gv$sqlarea）

Oracle查询锁定表的会话信息，可以执行下面的SQL来进行查询：

SELECT s.sid
      ,s.serial#,p.spid
      ,s.username
      ,s.osuser
      ,s.program
      ,s.module
      ,s.action,s.logon_time
      ,s.type,a.sql_text
  FROM gv$session s
      ,gv$process p
      ,gv$sqlarea a
 WHERE s.paddr = p.addr
   AND s.sql_id = a.sql_id
   AND s.status='ACTIVE'AND s.username ISNOTNULLAND s.type!='BACKGROUND'AND a.sql_text NOTLIKE'%gv$sqlarea a%'ORDERBY s.logon_time DESC;

3.4、gv$lock视图

此查询将返回被锁定的表的会话ID、用户名、机器名、锁模式、锁定类型以及锁定对象的ID等信息。请注意，如果有多个锁定类型，则此查询可能会返回多行。

SELECT s.sid
      ,s.serial#,s.username
      ,s.osuser
      ,s.machine
      ,l.type,l.block
      ,l.id1
      ,l.id2
      ,a.SQL_TEXT
      ,CASEWHEN o1.object_name ISNOTNULLTHEN o1.owner||'.'||o1.object_name
         ELSENULLENDAS id1_object_name
      ,CASEWHEN o2.object_name ISNOTNULLTHEN o2.owner||'.'||o2.object_name
         ELSENULLENDAS id2_object_name
  FROM gv$session s
      ,gv$lock    l
      ,gv$sqlarea a
      ,dba_objects o1
      ,dba_objects o2
 WHERE s.sid = l.sid
   AND s.sql_id = a.sql_id
   AND l.id1 = o1.OBJECT_ID(+)AND l.id2 = o2.OBJECT_ID(+)AND a.SQL_TEXT NOTLIKE'%,gv$sqlarea a%';

查询结果如下图：