non

select (select l.ORACLE_USERNAME
          from v$session_wait  sw,
               v$locked_object l,
               v$session       s,
               v$sqlarea       d,
               v$lock          a,
               v$lock          b
         where s.sid = a.sid
           and a.block in (1, 2)
           and b.BLOCK = 0
           and b.REQUEST > 0
           and a.ID1 = b.ID1
           and a.ID2 = b.ID2
           and rownum < 2) oracle_username,
       a.sid,
       (select s.SERIAL#
          from v$session_wait  sw,
               v$locked_object l,
               v$session       s,
               v$sqlarea       d,
               v$lock          a,
               v$lock          b
         where s.sid = a.sid
           and a.block in (1, 2)
           and b.BLOCK = 0
           and b.REQUEST > 0
           and a.ID1 = b.ID1
           and a.ID2 = b.ID2
           and rownum < 2) SERIAL#,
       (select l.OS_USER_NAME
          from v$session_wait  sw,
               v$locked_object l,
               v$session       s,
               v$sqlarea       d,
               v$lock          a,
               v$lock          b
         where s.sid = a.sid
           and a.block in (1, 2)
           and b.BLOCK = 0
           and b.REQUEST > 0
           and a.ID1 = b.ID1
           and a.ID2 = b.ID2
           and rownum < 2) os_user_name,
       (select s.MACHINE
          from v$session_wait  sw,
               v$locked_object l,
               v$session       s,
               v$sqlarea       d,
               v$lock          a,
               v$lock          b
         where s.sid = a.sid
           and a.block in (1, 2)
           and b.BLOCK = 0
           and b.REQUEST > 0
           and a.ID1 = b.ID1
           and a.ID2 = b.ID2
           and rownum < 2) machine,
       (select s.TERMINAL
          from v$session_wait  sw,
               v$locked_object l,
               v$session       s,
               v$sqlarea       d,
               v$lock          a,
               v$lock          b
         where s.sid = a.sid
           and a.block in (1, 2)
           and b.BLOCK = 0
           and b.REQUEST > 0
           and a.ID1 = b.ID1
           and a.ID2 = b.ID2
           and rownum < 2) terminal,
           (     
      select d.SQL_TEXT
          from v$session s, v$sqlarea d, v$lock a, v$locked_object l
         where s.sid =a.sid
         and l.SESSION_ID=s.SID
         and  s.prev_sql_addr = d.address
           and rownum < 2
           ) sqltext,
       ' is blocking ',
       (select s.username
          from v$session s, v$sqlarea d, v$lock b
         where s.sid = b.sid
           and s.SQL_ADDRESS = d.ADDRESS
           and d.SQL_TEXT like '%for update%'
           and rownum < 2) blockee,
       b.sid,
       (select s.SERIAL#
          from v$session_wait  sw,
               v$locked_object l,
               v$session       s,
               v$sqlarea       d,
               v$lock          a,
               v$lock          b
         where s.sid = b.sid
           and a.block in (1, 2)
           and b.BLOCK = 0
           and b.REQUEST > 0
           and a.ID1 = b.ID1
           and a.ID2 = b.ID2
           and rownum < 2) SERIAL#,
       (select d.SQL_TEXT
          from v$session s, v$sqlarea d, v$lock b
         where s.sid = b.sid
           and s.SQL_ADDRESS = d.ADDRESS
           and d.SQL_TEXT like '%for update%'
           and rownum < 2) CURRENTWAITSQL,
       (select a.CTIME
                  from v$session_wait  sw,
                       v$locked_object l,
                       v$session       s,
                       v$sqlarea       d,
                       v$lock          a,
                       v$lock          b
                 where s.sid = b.sid
                   and a.block in (1, 2)
                   and b.BLOCK = 0
                   and b.REQUEST > 0
                   and a.ID1 = b.ID1
                   and a.ID2 = b.ID2
                   and rownum<2
                   and a.CTIME/60>60
                   ) zusaishijian
  from v$lock a, v$lock b
 where a.block = 1
   and b.request > 0
   and a.id1 = b.id1
   and a.id2 = b.id2
    

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

转载于:http://blog.itpub.net/13750068/viewspace-720690/