Oracle查锁表(史上最全)
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锁的详情的查询结果如下图所示:
https://img-blog.csdnimg.cn/a0e1326fa9f349a48ff851f99753c1b8.png
1.2、解锁表的DDL锁
有两种方式可以解锁表的DDL锁。
[*]一是:实行kill session脚本。
[*]二是:调用tzq_server_pkg包的kill_session存过实行杀会话kill session。
1.2.1、解锁表的DDL锁 - 1、实行kill session脚本
Ⅰ、打开下令窗口
https://img-blog.csdnimg.cn/59f1742cf3064bde8ab0bfe27e798b97.png
Ⅱ、实行上面天生好的kill session脚本
alter system kill session '314,93,@1' immediate;
https://img-blog.csdnimg.cn/0cd22847787b4551bbfbc6d80aaf017f.png
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锁的详情的查询结果如下图所示:
https://img-blog.csdnimg.cn/7a19335ad4fd42a089fbccb626eed43e.png
2.2、解锁表的DML锁
有两种方式可以解锁表的DML锁。
[*]一是:实行kill session脚本。
[*]二是:调用tzq_server_pkg包的kill_session存过实行杀会话kill session。
2.2.1、解锁表的DML锁 - 1、实行kill session脚本
Ⅰ、打开下令窗口
https://img-blog.csdnimg.cn/59f1742cf3064bde8ab0bfe27e798b97.png
Ⅱ、实行上面天生好的kill session脚本
alter system kill session '314,93,@1' immediate;
https://img-blog.csdnimg.cn/0cd22847787b4551bbfbc6d80aaf017f.png
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 %'
;
https://img-blog.csdnimg.cn/3083a2d600754380b5696669e74d3812.png
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'))
;
https://img-blog.csdnimg.cn/5c96517c6afd4a9c9c8e8a4b614a3b09.png
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;
https://img-blog.csdnimg.cn/1fd45c5a540b44d39b3aa9d20aeac90c.png
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%'
;
查询结果如下图:
https://img-blog.csdnimg.cn/f86bfe81dfef47cabb7b3e03b6915894.png
https://img-blog.csdnimg.cn/f2be48eba0144185a8123bf841ca04e7.png
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。
页:
[1]