Join v$rollname, v$rollstat, v$session c, v$sqltext and v$transaction : 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.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>