Oracle查锁表（史上最全）

2024-03-19 1378阅读

温馨提示：这篇文章已超过387天没有更新，请注意相关的内容是否还可用！

Oracle查锁表

  • Oracle分两种锁，一种是DDL锁，一种是DML锁。
  • 一、Oracle DDL锁的解锁（dba_ddl_locks视图）
    • 1.1、查表的DDL锁的详情（kill session脚本、表名、执行锁表的SQL等）
    • 1.2、解锁表的DDL锁
      • 1.2.1、解锁表的DDL锁 - 1、执行kill session脚本
        • Ⅰ、打开命令窗口
        • Ⅱ、执行上面生成好的kill session脚本
        • 1.2.2、解锁表的DDL锁 - 2、调用tzq_server_pkg包的kill_session存过执行杀会话kill session。
        • 二、Oracle DML锁的解锁（gv$locked_object视图）
          • 2.1、查表的DML锁的详情（kill session脚本、表名、执行锁表的SQL等）
          • 2.2、解锁表的DML锁
            • 2.2.1、解锁表的DML锁 - 1、执行kill session脚本
              • Ⅰ、打开命令窗口
              • Ⅱ、执行上面生成好的kill session脚本
              • 2.2.2、解锁表的DML锁 - 2、调用tzq_server_pkg包的kill_session存过执行杀会话kill session。
              • 三、附录
                • 3.1、根据sid查sql_text（gv\$session、gv$sqlarea）
                • 3.2、查锁表的详情（dba_locks视图）
                • 3.3、Oracle查询锁定表的会话信息（gv\$session、gv\$process、gv$sqlarea）
                • 3.4、gv$lock视图

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

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

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

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

                  SELECT DISTINCT '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
 WHERE 1 = 1
   AND l.session_id = s.sid
   AND s.sql_id = a.sql_id
   AND lower(a.sql_text) NOT LIKE '%alter system kill session %'
-- AND l.owner IN ('TZQ','LOG')
;

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

                  Oracle查锁表（史上最全）

                  1.2、解锁表的DDL锁

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

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

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

                    Ⅰ、打开命令窗口

                    Oracle查锁表（史上最全）

                    Ⅱ、执行上面生成好的kill session脚本
                    alter system kill session '314,93,@1' immediate;

                    Oracle查锁表（史上最全）

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

                    tzq_server_pkg包的代码详见博客：Oracle解锁表、包、用户、杀会话、停job

                    打开命令行窗口，执行下面命令：

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

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

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

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

                    SELECT DISTINCT '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锁的详情的查询结果如下图所示：

                    Oracle查锁表（史上最全）

                    2.2、解锁表的DML锁

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

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

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

                      Ⅰ、打开命令窗口

                      Oracle查锁表（史上最全）

                      Ⅱ、执行上面生成好的kill session脚本
                      alter system kill session '314,93,@1' immediate;

                      Oracle查锁表（史上最全）

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

                      tzq_server_pkg包的代码详见博客：Oracle解锁表、包、用户、杀会话、停job

                      打开命令行窗口，执行下面命令：

                      set serveroutput on
execute 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
  LEFT JOIN gv$sqlarea a
    ON s.sql_id = a.sql_id
 WHERE s.sql_id IS NOT NULL
   AND a.sql_text NOT LIKE '%AND a.sql_text NOT LIKE %'
 ;

                      Oracle查锁表（史上最全）

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

                      SELECT DISTINCT '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
      ,CASE
         WHEN o1.object_name IS NOT NULL
           THEN o1.owner||'.'||o1.object_name
         ELSE NULL
       END AS id1_object_name
      ,CASE
         WHEN o2.object_name IS NOT NULL
           THEN o2.owner||'.'||o2.object_name
         ELSE NULL
       END AS id2_object_name
      ,l.last_convert
      ,l.blocking_others
      ,a.SQL_TEXT
  FROM dba_locks l
  LEFT JOIN dba_objects o1
    ON l.lock_id1 = o1.OBJECT_ID
  LEFT JOIN dba_objects o2
    ON l.lock_id2 = o2.OBJECT_ID
  LEFT JOIN gv$session s
    ON l.session_id = s.SID
  LEFT JOIN v$sqlarea a
    ON s.sql_id = a.sql_id
 WHERE 1=1
   AND a.SQL_TEXT IS NOT NULL
   AND (o1.owner IN ('TZQ','LOG') OR
        o2.owner IN ('TZQ','LOG'))
;

                      Oracle查锁表（史上最全）

                      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 IS NOT NULL
   AND s.type != 'BACKGROUND'
   AND a.sql_text NOT LIKE '%gv$sqlarea a%'
 ORDER BY s.logon_time DESC;

                      Oracle查锁表（史上最全）

                      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
      ,CASE
         WHEN o1.object_name IS NOT NULL
           THEN o1.owner||'.'||o1.object_name
         ELSE NULL
       END AS id1_object_name
      ,CASE
         WHEN o2.object_name IS NOT NULL
           THEN o2.owner||'.'||o2.object_name
         ELSE NULL
       END AS 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 NOT LIKE '%,gv$sqlarea a%'
;

                      查询结果如下图：

                      Oracle查锁表（史上最全）

                      Oracle查锁表（史上最全）

VPS购买请点击我

免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们，邮箱：ciyunidc@ciyunshuju.com。本站只作为美观性配图使用,无任何非法侵犯第三方意图,一切解释权归图片著作权方,本站不承担任何责任。如有恶意碰瓷者,必当奉陪到底严惩不贷!

相关阅读

目录[+]