Join v$session, v$lock and v$sqltext with subquery : sqltext « System Tables Data Dictionary « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Tables Data Dictionary
3. sqltext

SQL>
SQL> SELECT a.serial#, a.sid, a.username, b.id1, c.sql_text
  2  FROM   v$session a, v$lock b, v$sqltext c
  3  WHERE  b.id1 IN
  4  (SELECT DISTINCT e.id1
  5  FROM    v$session d, v$lock e
  6  WHERE   d.lockwait   = e.kaddr)
  7  AND     a.sid        = b.sid
  8  AND     c.hash_value = a.sql_hash_value
  9  AND     b.request    = 0
 10  and     rownum < 2;

no rows selected

SQL>
SQL>
SQL>