Oracle死锁信息查询

--1.

select count(*) from v$process;
select value from v$parameter where name = 'processes';
select value from v$parameter where name = 'sessions';

select sid,serial#,username,program,machine,status from v$session ORDER BY status;

---- 查看会话
select * from v$session WHERE STATUS = 'INACTIVE'
select a.sql_text from v$session v,v$sqlarea a WHERE v.prev_sql_addr = a.address AND STATUS = 'INACTIVE'

--2.查询那些应用的连接数此时是多少
select b.MACHINE,b.PROGRAM,count(*) from v$process a,v$session b where a.ADDR = b.PADDR and b.USERNAME is not null group by b.MACHINE,b.PROGRAM order by count(*) desc;

---3.查询是什么SQL引起了锁表的原因
select l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
s.user#,
l.os_user_name,
s.machine,
s.terminal,
a.sql_text,
a.action
from v$sqlarea a,v$session s,v$locked_object l
where l.session_id = s.sid
and s.prev_sql_addr = a.address
order by sid,s.serial#;

---4.查询是否有死锁
SELECT l.session_id sid,
l.locked_mode 锁模式,
l.oracle_username 登录用户,
l.os_user_name 登录机器用户名,
s.machine 机器名,
s.program,s.status,
s.terminal 终端用户名,
o.object_name 被锁对象名,
s.logon_time 登录数据库时间,
'ALTER system kill session ''' || l.session_id ||','|| s.serial#||''' ;' AS 解锁执行SQL
FROM v$locked_object l,all_objects o,v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid,s.serial#;

--- 如果想知道具体是哪个进程阻塞了哪个进程,可用以下语句查看:
select username,v$lock.sid,trunc(id1/power(2,16)) rbs,bitand(id1,to_number('ffff','xxxx'))+0 slot,id2 seq,lmode,request from v$lock,v$session
where v$lock.type = 'TX' and v$lock.sid = v$session.sid and v$session.username = 'ACC06' ;

select
(select username||':'||sid||':'||serial# from v$session where sid=a.sid) ||
' 阻塞了 ' ||
(select username ||':'||sid||':'||serial# from v$session where sid=b.sid) AS 阻塞信息
from v$lock a,v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

---查询造成死锁的sql语句 > 根据第一条语句获得的sql_id，查询对应的sql语句
select sid,sql_id,blocking_session,event,p1text,p1,wait_class,state,username from v$session where event like 'enq%'

select sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = 'fnw0ruh6x0zmg';

---查询所有死锁SQL： select sql_text,s.ELAPSED_TIME/1000000 from v$sql s where sql_id in (select sql_id from v$session where event like 'enq%') ;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!