查看Oracle中是否有锁表

最新推荐文章于 2026-04-16 02:40:09 发布

翻译最新推荐文章于 2026-04-16 02:40:09 发布 · 7.3k 阅读

标签

#Java #sql #dba #oracle #kill

java 同时被 3 个专栏收录

16 篇文章

订阅专栏

oracle

2 篇文章

订阅专栏

sql

2 篇文章

订阅专栏

本文介绍如何使用SQL查询来诊断和解决Oracle数据库中出现的锁问题，包括查看锁表、被锁的表、导致死锁的用户进程、连接的进程、引发锁的会话以及如何结束进程。

1、查看是否有锁表的sql

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、查看被锁的表

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、查看那个用户那个进程照成死锁，锁www.2cto.com的级别

select b.owner,b.object_name,l.session_id,l.locked_mode fromv$locked_object l, dba_objects b whereb.object_id=l.object_id;

4、查看连接的进程

SELECT sid, serial#, username, osuser FROMv$session;

5、查看是哪个session引起的

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、杀掉进程

alter   system    kill   session  'sid,serial#';

sid是第5步查询出的sid和serid