如何通过v$lock和v$locked和v$session 察看oracle锁情况

fanzerca

如何通过v$lock和v$locked和v$session 察看oracle锁情况

在v$lock 哪个字段什么状态标志是锁呢，请大虾指教

xiao_cui2000

1. 
   
2. column sid format 999;
   
3. column b format 9;
   
4. column object_name format a30;
   
5. select v$lock.sid,
   
6. decode(v$lock.type,
   
7.         'MR', 'Media Recovery',
   
8.         'RT','Redo Thread',
   
9.         'UN','User Name',
   
10.         'TX', 'Transaction',
    
11.         'TM', 'DML',
    
12.         'UL', 'PL/SQL User Lock',
    
13.         'DX', 'Distributed Xaction',
    
14.         'CF', 'Control File',
    
15.         'IS', 'Instance State',
    
16.         'FS', 'File Set',
    
17.         'IR', 'Instance Recovery',
    
18.         'ST', 'Disk Space Transaction',
    
19.         'TS', 'Temp Segment',
    
20.         'IV', 'Library Cache Invalida-tion',
    
21.         'LS', 'Log Start or Switch',
    
22.         'RW', 'Row Wait',
    
23.         'SQ', 'Sequence Number',
    
24.         'TE', 'Extend Table',
    
25.         'TT', 'Temp Table',
    
26.         'Unknown') LockType,
    
27. rtrim(object_type) || ' ' || rtrim(owner) || '.' || object_name object_name,
    
28. decode(lmode,   0, 'None',
    
29.                 1, 'Null',
    
30.                 2, 'Row-S',
    
31.                 3, 'Row-X',
    
32.                 4, 'Share',
    
33.                 5, 'S/Row-X',
    
34.                 6, 'Exclusive',        'Unknown') LockMode,
    
35. decode(request, 0, 'None',
    
36.                 1, 'Null',
    
37.                 2, 'Row-S',
    
38.                 3, 'Row-X',
    
39.                 4, 'Share',
    
40.                 5, 'S/Row-X',
    
41.                 6, 'Exclusive', 'Unknown') RequestMode,
    
42. ctime, block b
    
43. from v$lock, all_objects
    
44. where sid > 6
    
45. and v$lock.id1 = all_objects.object_id;
    

复制代码

xiao_cui2000

1. 
   
2. select 'alter system kill session '''||a.sid||','||b.serial#||''';'
   
3. from v$lock a,all_objects c,v$session b
   
4. where a.sid > 6 and a.id1 = c.object_id and a.sid = b.sid;
   
5. 
   
6. 
   
7. 
   
8. select 'alter system kill session '''||a.sid||','||b.serial#||''';'
   
9.     from v$lock a,v$session b
   
10.     where a.sid > 6 and a.sid = b.sid;
    

复制代码

[ 本帖最后由 xiao_cui2000 于 2006-7-4 09:46 编辑 ]

fanzerca

谢谢，

fanzerca

还想问下，他们和dba_object 关联的时候是什么意思呢 ?

jametong

得到被锁的表的object_name

fanzerca

还是不太明白,是不是如果有锁出现会纪录在v$lock表里面，然后关联到dba_object表查出具体object_name?

xiao_cui2000

原帖由 fanzerca 于 2006-7-3 18:22 发表
还是不太明白,是不是如果有锁出现会纪录在v$lock表里面，然后关联到dba_object表查出具体object_name?

是这个意思.因为第一个SQL要显示出哪个表被锁.所以到all_objects中查找
第二个sql可以不用.我改一下

xiao_cui2000

奇怪,我把第二个sql的all_objects去掉后,sql执行半天也不出结果

seeyouknowme

空闲之际，写了个sql语句，解决当前工作中经常遇到的锁定和被锁定表得信息，欢迎大家使用，指正。
如何快捷的显示当前锁定和被锁定表的用户，sql等信息
如果是dba用户那么可以使用
一、
阻塞和等待的用户及sql语句
select 'blocker('||wb.holding_session||':'||sb.username||')-sql:'||qb.sql_text blockers,sb.machine,sb.terminal,
       'waiter ('||wb.waiting_session||':'||sw.username||')-sql:'||qw.sql_text waiters ,sw.machine,sb.terminal
from  dba_waiters wb,
      v$session   sb,
      v$session   sw,
      v$sqlarea   qb,
      v$sqlarea   qw
where wb.holding_session=sb.sid
and   wb.waiting_session=sw.sid
and   sb.prev_sql_addr=qb.address
and   sw.sql_address=qw.address
and   wb.mode_held<>'None'

二、如果不是dba那么就不能使用dba_waiters表了，可以使用v$lock来替换

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
如果向更详细的显示username的信息可以加入


select 'blocker('||lb.sid||':'||sb.username||')-sql:'|| qb.sql_text  blockers,sb.machine,sb.terminal,sb.logon_time,
       'waiter ('||lw.sid||':'||sw.username||')-sql:'|| qw.sql_text  waiters ,sw.machine,sw.terminal,sw.logon_time
      

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

三、--根据以上显示的信息可以知道当前产生锁和等待的用户信息
--那么就需要杀掉当前产生阻塞的用户，在执行杀掉用户进程前请咨询dba和该业务的同事，是否产生其他的不当影响

select sid,serial# from v$session sid in (...)  --查询产生阻塞用户的  sid,serial#,然后
alter system kill session(sid,serial#);   --杀掉产生阻塞的用户