Oracle查锁表的实现(史上最全)

admin Oracle

2024-03-01 0 404

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

1.2、解锁表的DDL锁

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

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

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

Ⅰ、打开命令窗口

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

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

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

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

2.2、解锁表的DML锁

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

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

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

Ⅰ、打开命令窗口

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

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

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

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 %\’
;

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\’))
;

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;

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%\’
;