oracle死锁、会话、阻塞等常用sql命令查询

1.查询数据库中的锁

select * from v$lock;
select * from v$lock where block=1;

2.查询被锁的对象

select * from v$locked_object;

3.查询阻塞
查被阻塞的会话
select * from v$lock where lmode=0 and type in ('TM','TX');

查阻塞别的会话锁
select * from v$lock where lmode>0 and type in ('TM','TX');

4.查询数据库正在等待锁的进程
select * from v$session where lockwait is not null;

5.查询会话之间锁等待的关系
select a.sid holdsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime from v$lock a,v$lock b
where a.id1=b.id1 and a.id2=b.id2 and a.block=1 and b.block=0;

6.查询锁等待事件
select * from v$session_wait where event='enqueue';

解决方案：
--首先得到被锁对象的session_id
select session_id from v$locked_object;

--通过上面得到的session_id去取得v$session的sid和serial#，然后对该进程进行终止。
SELECT sid, serial#, username, osuser FROM v$session where sid = session_id;

--最后杀会话
ALTER SYSTEM KILL SESSION 'sid,serial';
 

来自：http://www.ttwdsq.com/article/3661