Oracle数据库死锁排查

2019-06-07 本文已影响0人 FrankDaddy

需要用有admin权限的账号登录plsql
语法如下：
--查看被锁的表
select b.owner,b.object_name,a.session_id,a.inst_id,a.locked_mode from gv $locked_object a,dba_objects b where b.object_id = a.object_id; --查看那个用户那个进程照成死锁 select b.username,b.sid,b.serial#,b.inst_id,logon_time from gv$ locked_object a,gv $session b where a.session_id = b.sid and a.inst_id = b.isnt_id order by b.logon_time; --查看连接的进程 SELECT sid, serial#, inst_id,username, osuser FROM gv$ session;
--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
select s.sid,
s.serial#,
s.username,
s.schemaname,
s.osuser,
s.process,
s.machine,
s.terminal,
s.logon_time,
l.type
FROM v $session s, v$ lock l
WHERE s.sid = l.sid AND s.username IS NOT NULL ORDER BY sid;

最后，kill的语法：
ALTER SYSTEM KILL SESSION 'sid,serial#'
ALTER SYSTEM KILL SESSION 'sid,serial#，@inst_id'

Oracle数据库死锁排查

猜你喜欢

热点阅读