这篇文章将为大家详细讲解有关如何查看锁定的session信息脚本,小编觉得挺实用的,因此分享给大家做个参考,希望大家阅读完这篇文章后可以有所收获。
查看当前被阻塞的对象和锁信息
SELECT DISTINCT
s1.inst_id BlockingInst,
s1.sid BlockingSid,
s1.serial# BlockingSerial,
s2.inst_id BlockedInst,
s2.sid BlockedSid,
s2.username BlockedUser,
s2.seconds_in_wait BlockedWaitTime
FROM gv$session s1,
gv$lock l1,
gv$session s2,
gv$lock l2
WHERE s1.inst_id = l1.inst_id and
l1.block in (1, 2) and
l2.request != 0 and
l1.sid = s1.sid and
l1.id1 = l2.id1 and
l1.id2 = l2.id2 and
s2.sid = l2.sid and
s2.inst_id = l2.inst_id
ORDER BY 1, 2, 3
查看被锁的回话信息:
SELECT
s.blocking_session,
s.sid,
s.osuser,
s.seconds_in_wait,
s.event,
s.state,
t.sql_text,
o.object_name
FROM
v$session s, v$sqltext t, v$locked_object lo, dba_objects o
WHERE
blocking_session IS NOT NULL
and t.hash_value=s.sql_hash_value
and s.sid=lo.session_id
and lo.object_id=o.object_id
ORDER BY t.hash_value,t.PIECE;
以DBA角色, 查看当前数据库里锁的情况可以用如下SQL语句:
col owner for a12
col object_name for a16
select b.owner,b.object_name,l.session_id,l.locked_mode
from v$locked_object l, dba_objects b
where b.object_id=l.object_id
/
select t2.username,t2.sid,t2.serial#,t2.logon_time ,t1.object_id,t3.object_name,t1.locked_mode
from v$locked_object t1,v$session t2 ,dba_objects t3
where t1.session_id=t2.sid and t1.object_id=t3.object_id order by t2.logon_time
/
如果有长期出现的一列,可能是没有释放的锁。我们可以用下面SQL语句杀掉长期没有释放非正常的锁:
alter system kill session 'sid,serial#';
另外几个可以查询阻塞回话的视图:
DBA_WAITERS
DBA_BLOCKERS
V$WAIT_CHAINS
关于“如何查看锁定的session信息脚本”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,使各位可以学到更多知识,如果觉得文章不错,请把它分享出去让更多的人看到。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。