Oracle查锁表（史上最全）

---

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

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

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

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

1. SELECT DISTINCT 'alter system kill session ''' || s.sid || ',' || s.serial# || ',@' ||
2.                 s.inst_id || ''' immediate;' AS kill_session_scripts
3.                ,s.sql_id
4.                ,a.sql_text
5.                ,s.sid
6.                ,s.serial#
7.   FROM dba_ddl_locks l
8.       ,gv$session    s
9.       ,gv$sqlarea     a
10. WHERE 1 = 1
11.    AND l.session_id = s.sid
12.    AND s.sql_id = a.sql_id
13.    AND lower(a.sql_text) NOT LIKE '%alter system kill session %'
14. -- AND l.owner IN ('TZQ','LOG')
15. ;

复制代码

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

1.2、解锁表的DDL锁

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

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

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

Ⅰ、打开下令窗口

Ⅱ、实行上面天生好的kill session脚本

1. alter system kill session '314,93,@1' immediate;

复制代码

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

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

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

复制代码

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

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

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

1. SELECT DISTINCT 'alter system kill session ''' || s.sid || ',' || s.serial# || ',@' ||
2.                 s.inst_id || ''' immediate;' AS kill_session_scripts
3.                ,o.owner
4.                ,o.object_name
5.                ,s.sql_id
6.                ,a.sql_text
7.                ,s.sid
8.                ,s.serial#
9.   FROM gv$locked_object l
10.       ,dba_objects      o
11.       ,gv$session       s
12.       ,gv$sqlarea        a
13. WHERE l.object_id = o.object_id
14.    AND l.session_id = s.sid
15.    AND l.inst_id = s.inst_id
16.    AND s.sql_id = a.sql_id
17.    -- AND o.owner IN ('TZQ','LOG')
18. ;

复制代码

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

2.2、解锁表的DML锁

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

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

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

Ⅰ、打开下令窗口

Ⅱ、实行上面天生好的kill session脚本

1. alter system kill session '314,93,@1' immediate;

复制代码

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

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

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

复制代码

三、附录

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

1. SELECT s.sid
2.       ,s.serial#
3.       ,s.sql_id
4.       ,s.sql_hash_value
5.       ,s.username
6.       ,a.sql_text
7.   FROM gv$session s
8.   LEFT JOIN gv$sqlarea a
9.     ON s.sql_id = a.sql_id
10. WHERE s.sql_id IS NOT NULL
11.    AND a.sql_text NOT LIKE '%AND a.sql_text NOT LIKE %'
12. ;

复制代码

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

1. SELECT DISTINCT 'alter system kill session ''' || s.sid || ',' || s.serial# || ',@' ||
2.                 s.inst_id || ''' immediate;' AS kill_session_scripts
3.       ,l.session_id
4.       ,s.serial#
5.       ,l.lock_TYPE
6.       ,l.mode_held
7.       ,l.mode_requested
8.       ,CASE
9.          WHEN o1.object_name IS NOT NULL
10.            THEN o1.owner||'.'||o1.object_name
11.          ELSE NULL
12.        END AS id1_object_name
13.       ,CASE
14.          WHEN o2.object_name IS NOT NULL
15.            THEN o2.owner||'.'||o2.object_name
16.          ELSE NULL
17.        END AS id2_object_name
18.       ,l.last_convert
19.       ,l.blocking_others
20.       ,a.SQL_TEXT
21.   FROM dba_locks l
22.   LEFT JOIN dba_objects o1
23.     ON l.lock_id1 = o1.OBJECT_ID
24.   LEFT JOIN dba_objects o2
25.     ON l.lock_id2 = o2.OBJECT_ID
26.   LEFT JOIN gv$session s
27.     ON l.session_id = s.SID
28.   LEFT JOIN v$sqlarea a
29.     ON s.sql_id = a.sql_id
30. WHERE 1=1
31.    AND a.SQL_TEXT IS NOT NULL
32.    AND (o1.owner IN ('TZQ','LOG') OR
33.         o2.owner IN ('TZQ','LOG'))
34. ;

复制代码

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

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

1. SELECT s.sid
2.       ,s.serial#
3.       ,p.spid
4.       ,s.username
5.       ,s.osuser
6.       ,s.program
7.       ,s.module
8.       ,s.action
9.       ,s.logon_time
10.       ,s.type
11.       ,a.sql_text
12.   FROM gv$session s
13.       ,gv$process p
14.       ,gv$sqlarea a
15. WHERE s.paddr = p.addr
16.    AND s.sql_id = a.sql_id
17.    AND s.status = 'ACTIVE'
18.    AND s.username IS NOT NULL
19.    AND s.type != 'BACKGROUND'
20.    AND a.sql_text NOT LIKE '%gv$sqlarea a%'
21. ORDER BY s.logon_time DESC;

复制代码

3.4、gv$lock视图

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

1. SELECT s.sid
2.       ,s.serial#
3.       ,s.username
4.       ,s.osuser
5.       ,s.machine
6.       ,l.type
7.       ,l.block
8.       ,l.id1
9.       ,l.id2
10.       ,a.SQL_TEXT
11.       ,CASE
12.          WHEN o1.object_name IS NOT NULL
13.            THEN o1.owner||'.'||o1.object_name
14.          ELSE NULL
15.        END AS id1_object_name
16.       ,CASE
17.          WHEN o2.object_name IS NOT NULL
18.            THEN o2.owner||'.'||o2.object_name
19.          ELSE NULL
20.        END AS id2_object_name
21.   FROM gv$session s
22.       ,gv$lock    l
23.       ,gv$sqlarea a
24.       ,dba_objects o1
25.       ,dba_objects o2
26. WHERE s.sid = l.sid
27.    AND s.sql_id = a.sql_id
28.    AND l.id1 = o1.OBJECT_ID(+)
29.    AND l.id2 = o2.OBJECT_ID(+)
30.    AND a.SQL_TEXT NOT LIKE '%,gv$sqlarea a%'
31. ;

复制代码

查询结果如下图：

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。