ORACLE 11g SQL 锁表查询

锁表查询
 
   SELECT p.spid,
      c.object_name,
      b.session_id,
      a.serial#,
      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;
 -- 解锁：
alter system kill session'b.session_id,a.serial#';
ALTER system kill session '23, 1647';

  --2、查询当前表锁的方法：
  SELECT all_objects.object_name,
      s.sid,
      s.serial#,
      s.osuser,
      s.program,
      s.machine,
      s.client_info
 FROM v$lock k, v$session s, all_objects
 WHERE k.sid = s.sid
  AND k.TYPE IN('TX','TM')
  AND k.id1 = all_objects.object_id;
 
--存在锁请求，即被阻塞
SELECT sn.username,
       m.sid,
       sn.serial#,
       m.TYPE,
       decode(m.lmode,
              0,
              'None',
              1,
              'Null',
              2,
              'Row Share',
              3,
              'Row Excl.',
              4,
              'Share',
              5,
              'S/Row Excl.',
              6,
              'Exclusive',
              lmode,
              ltrim(to_char(lmode, '990'))) lmode,
       decode(m.request,
              0,
              'None',
              1,
              'Null',
              2,
              'Row Share',
              3,
              'Row Excl.',
              4,
              'Share',
              5,
              'S/Row Excl.',
              6,
              'Exclusive',
              request,
              ltrim(to_char(m.request, '990'))) request,
       m.id1,
       m.id2
  FROM v$session sn, v$lock m
 WHERE sn.sid = m.sid
   AND m.request != 0
 ORDER BY id1, id2, m.request;
 
 --不存在锁请求，但是锁定的对象被其他会话请求锁定
SELECT sn.username,
      m.sid,
      sn.serial#,
      m.TYPE,
      decode(m.lmode,
             0,
             'None',
             1,
             'Null',
             2,
             'Row Share',
             3,
             'Row Excl.',
             4,
             'Share',
             5,
             'S/Row Excl.',
             6,
             'Exclusive',
             lmode,
             ltrim(to_char(lmode,'990'))) lmode,
      decode(m.request,
             0,
             'None',
             1,
             'Null',
             2,
             'Row Share',
             3,
             'Row Excl.',
             4,
             'Share',
             5,
             'S/Row Excl.',
             6,
             'Exclusive',
             request,
             ltrim(to_char(m.request,'990'))) request,
      m.id1,
      m.id2
 FROM v$session sn, v$lock m
 WHERE sn.sid = m.sid
  AND m.request =0
  AND m.lmode !=4
  AND(id1, id2)IN (SELECTs.id1, s.id2
                       FROM v$lock s
                      WHERE request !=0
                        AND s.id1 = m.id1
                        AND s.id2 = m.id2)
 ORDER By id1,id2,m.request'

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18900329/viewspace-1743640/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18900329/viewspace-1743640/