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

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

   <source lang="sql">

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></source>

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

   <source lang="sql">

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></source>

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

   <source lang="sql">

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></source>