频道导航

查看Oracle中是否有锁表的sql

2019-01-03 Oracle 前端之家

前端之家收集整理的这篇文章主要介绍了查看Oracle中是否有锁表的sql，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

1、查看是否有锁表的sql @H_301_0@<div class="codetitle"><a style="CURSOR: pointer" data="25873" class="copybut" id="copybut25873" onclick="doCopy('code25873')"> 代码如下:

<div class="codebody" id="code25873"> @H_301_0@select 'blocker('||lb.sid||':'||sb.username||')-sql:'|| qb.sqltext blockers,@H301_0@'waiter ('||lw.sid||':'||sw.username||')-sql:'|| qw.sqltext waiters @H3010@from v$lock lb,@H3010@v$lock lw,@H3010@v$session sb,@H3010@v$session sw,@H301_0@v$sql qb,@H_301_0@v$sql qw @H_3010@where lb.sid=sb.sid @H3010@and lw.sid=sw.sid @H3010@and sb.prevsqladdr=qb.address @H301_0@and sw.sqladdress=qw.address @H3010@and lb.id1=lw.id1 @H3010@and sw.lockwait is not null @H3010@and sb.lockwait is null @H3010@and lb.block=1 ; @H3010@

@H3010@2、查看被锁的表 @H301_0@<div class="codetitle"><a style="CURSOR: pointer" data="17588" class="copybut" id="copybut17588" onclick="doCopy('code17588')"> 代码如下:

<div class="codebody" id="code17588"> @H_301_0@select p.spid,a.serial#,c.object_name,b.session_id,b.oracle_username,b.os_username @H301_0@from v$process p,v$session a,v$locked_object b,allobjects c @H301_0@where p.addr=a.paddr and a.process=b.process and c.object_id=b.objectid ; @H3010@

@H301_0@3、查看那个用户那个进程造成死锁，锁的级别 @H_301_0@<div class="codetitle"><a style="CURSOR: pointer" data="95561" class="copybut" id="copybut95561" onclick="doCopy('code95561')"> 代码如下:

<div class="codebody" id="code95561"> @H_301_0@select b.owner,b.object_name,l.session_id,l.locked_mode fromv$locked_object l,dbaobjects @H3010@

@H3010@4、查看连接的进程 @H301_0@<div class="codetitle"><a style="CURSOR: pointer" data="35082" class="copybut" id="copybut35082" onclick="doCopy('code35082')"> 代码如下:

<div class="codebody" id="code35082"> @H_3010@SELECT sid,serial#,username,osuser FROMv$session; @H3010@ @H3010@5、查看是哪个session引起的 @H301_0@<div class="codetitle"><a style="CURSOR: pointer" data="16519" class="copybut" id="copybut16519" onclick="doCopy('code16519')"> 代码如下:<div class="codebody" id="code16519"> @H_301_0@select b.username,b.sid,b.serial#,logontime @H301_0@from v$lockedobject a,v$session b @H301_0@where a.session_id = b.sid order by b.logontime; @H3010@ @H3010@6、杀掉进程 @H301_0@<div class="codetitle"><a style="CURSOR: pointer" data="98766" class="copybut" id="copybut98766" onclick="doCopy('code98766')"> 代码如下:<div class="codebody" id="code98766"> @H_3010@alter system kill session 'sid,serial#'; @H3010@ @H301_0@sid是第5步查询出的sid和serid

锁表