Friday, November 04, 2011

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.)

No comments: