查看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