title

SELECT s1.sid,
       t1.sql_text,
       s2.sid,
       t2.sql_text
  FROM v$lock l1
  join v$session s1
    on s1.sid = l1.sid
  join v$lock l2
    on l1.id1 = l2.id1
   and l1.id2 = l2.id2
  join v$session s2
    on s2.sid = l2.sid
  left join v$sqlarea t1
    on s1.sql_hash_value = t1.hash_value
  left join v$sqlarea t2
    on s2.sql_hash_value = t2.hash_value 
 where l2.request > 0 /* request > 0 代表被阻塞 */
   and l1.block = 1 /* block = 1 代表阻塞其他 */

如果sql較長就使用v$sqlarea，可以顯示完整的sql (用v$sqltext可能會只有片段)

使用v$session的lockwait也能關聯到v$lock的kaddr

參考 https://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_2027.htm#REFRN30121