Oracle PL/SQL Tutorial/System Tables Data Dictionary/sqltext

Join v$rollname, v$rollstat, v$session c, v$sqltext and v$transaction

SQL>
SQL> SELECT a.name, b.xacts tr, c.sid, c.serial#, c.username, d.sql_text
  2  FROM   v$rollname a, v$rollstat b, v$session c,
  3         v$sqltext d,v$transaction e
  4  WHERE  a.usn            = b.usn
  5  AND    b.usn            = e.xidusn
  6  AND    c.taddr          = e.addr
  7  AND    c.sql_address    = d.address
  8  AND    c.sql_hash_value = d.hash_value
  9  and    rownum < 2
 10  ORDER BY a.name, c.sid, d.piece;
no rows selected
SQL>

Join v$session, v$lock and v$sqltext

SQL>
SQL> SELECT b.username, b.serial#, d.id1, a.sql_text
  2  FROM   v$session b, v$lock d, v$sqltext a
  3  WHERE  b.lockwait   = d.kaddr
  4  and    rownum < 2
  5  AND    a.address    = b.sql_address
  6  AND    a.hash_value = b.sql_hash_value;
no rows selected
SQL>
SQL>

Join v$session, v$lock and v$sqltext with subquery

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>