如何得到持有数据库锁的SQL？在线等待！

Question

我现在的Oracle数据库里面，某些条件下会出现锁等待的情况，
现在持有锁的进程号知道，如何知道这个进程执行那个SQL的时候造成了数据库锁？

Answer 1

select a.username, a.sid, a.serial#, b.id1
      from v$session a, v$lock b
     where b.id1 in
           (select distinct e.id1
              from v$session d, v$lock e
             where d.lockwait = e.kaddr)
       and a.sid = b.sid
       and b.request = 0

Answer 2

SELECT vs.sid, 
       vs.username, 
       vs.logon_time, 
       vs.machine, 
       vs.sql_hash_value, 
       vsql.piece, 
       vsql.sql_text, 
       vs.prev_hash_value 
FROM V$SESSION vs, v$sqltext vsql 
WHERE SID IN 
 ( SELECT lpad(' ',DECODE(vl.request,0,0,1))||sid 
    FROM V$LOCK vl WHERE id1 IN 
     ( SELECT id1 FROM V$LOCK vl2 WHERE vl2.lmode = 0) ) 
AND vs.sql_hash_value = vsql.hash_value 
ORDER BY 2,5,6; 

Answer 3

to qfsb_p(我心飞翔) 
  已经知道持有锁的进程和被锁的进程。
to  ljian_mail(外星人)
  你给的SQL只能查出被锁（被阻塞）的进程的SQL，不能查出持有锁进程产生锁的SQL啊。

Answer 4

select a.username, a.sid, a.serial#, b.id1
  from v$session a, v$lock b
  where a.lockwait = b.kaddr

Answer 5

select user_name,sql_text from v$open_cursor where sid in (select sid from (select sid,serial#,username,program from v$session where status='ACTIVE'))

Answer 6

数据库的锁是比较耗费资源的，特别是发生锁等待的时候，我们必须找到发生等待的锁，有可能的话，杀掉该进程。
这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。
可以通过alter system kill session ‘sid,serial#’来杀掉会话
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL
如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待
以下的语句可以查询到谁锁了表，而谁在等待。
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC
以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN

Answer 7

SELECT S.SID SESSION_ID,s.MACHINE,s.SERIAL#,   
       S.USERNAME,
       DECODE(LMODE,
              0,
              'None',
              1,
              'Null',
              2,
              'Row-S (SS)',
              3,
              'Row-X (SX)',
              4,
              'Share',
              5,
              'S/Row-X (SSX)',
              6,
              'Exclusive',
              TO_CHAR(LMODE)) MODE_HELD,
       DECODE(REQUEST,
              0,
              'None',
              1,
              'Null',
              2,
              'Row-S (SS)',
              3,
              'Row-X (SX)',
              4,
              'Share',
              5,
              'S/Row-X (SSX)',
              6,
              'Exclusive',
              TO_CHAR(REQUEST)) MODE_REQUESTED,
       O.OWNER || '.' || O.OBJECT_NAME || ' (' || O.OBJECT_TYPE || ')',S.TYPE LOCK_TYPE,
       L.ID1 LOCK_ID1,
       L.ID2 LOCK_ID2
  FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S
 WHERE L.SID = S.SID
   AND L.ID1 = O.OBJECT_ID