|
1、查看是否有锁表的sql
<div class="codetitle"><a style="CURSOR: pointer" data="25873" class="copybut" id="copybut25873" onclick="doCopy('code25873')"> 代码如下:<div class="codebody" id="code25873">
select 'blocker('||lb.sid||':'||sb.username||')-sql:'|| qb.sql_text blockers,
'waiter ('||lw.sid||':'||sw.username||')-sql:'|| qw.sql_text waiters
from v$lock lb,
v$lock lw,
v$session sb,
v$session sw,
v$sql qb,
v$sql qw
where lb.sid=sb.sid
and lw.sid=sw.sid
and sb.prev_sql_addr=qb.address
and sw.sql_address=qw.address
and lb.id1=lw.id1
and sw.lockwait is not null
and sb.lockwait is null
and lb.block=1 ;
2、查看被锁的表
<div class="codetitle"><a style="CURSOR: pointer" data="17588" class="copybut" id="copybut17588" onclick="doCopy('code17588')"> 代码如下:<div class="codebody" id="code17588">
select p.spid,a.serial#,c.object_name,b.session_id,b.oracle_username,b.os_user_name
from v$process p,v$session a,v$locked_object b,all_objects c
where p.addr=a.paddr and a.process=b.process and c.object_id=b.object_id ;
3、查看那个用户那个进程造成死锁，锁的级别
<div class="codetitle"><a style="CURSOR: pointer" data="95561" class="copybut" id="copybut95561" onclick="doCopy('code95561')"> 代码如下:<div class="codebody" id="code95561">
select b.owner,b.object_name,l.session_id,l.locked_mode fromv$locked_object l,dba_objects
4、查看连接的进程
<div class="codetitle"><a style="CURSOR: pointer" data="35082" class="copybut" id="copybut35082" onclick="doCopy('code35082')"> 代码如下:<div class="codebody" id="code35082">
SELECT sid,serial#,username,osuser FROMv$session;
5、查看是哪个session引起的
<div class="codetitle"><a style="CURSOR: pointer" data="16519" class="copybut" id="copybut16519" onclick="doCopy('code16519')"> 代码如下:<div class="codebody" id="code16519">
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;
6、杀掉进程
<div class="codetitle"><a style="CURSOR: pointer" data="98766" class="copybut" id="copybut98766" onclick="doCopy('code98766')"> 代码如下:<div class="codebody" id="code98766">
alter system kill session 'sid,serial#';
sid是第5步查询出的sid和serid （编辑：莱芜站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
