How to See Another User's SQL

If one user runs some SQL:

 

SQL> show user

USER is "ORACLE"

SQL> l

  1  select to_char(sysdate,'HH24:MI:SS')

  2* Time_Now from dual

SQL> /

 

TIME_NOW

--------

16:04:40

 

SQL>

 

Another user can see it as follows:

 

SQL> show user

USER is "SYS"

SQL> l

  1  SELECT SQL_TEXT FROM V$SQL SQL, V$SESSION SES

  2  WHERE SQL.ADDRESS    = SES.SQL_ADDRESS

  3  AND   SQL.HASH_VALUE = SES.SQL_HASH_VALUE

  4* AND   USERNAME       = 'ORACLE'

SQL> /

 

SQL_TEXT

------------------------------------------------------------

select to_char(sysdate,'HH24:MI:SS') Time_Now from dual

 

SQL>

 

(The script depends on an entry in V$SESSION so the original user still needs to be logged in for it to work.)