title: Oracle queries executed by a session
body: <p>I am trying to trace the SQL statements executed against a particular database user. I don't have AUDITING enabled and I am using Oracle 11g.</p>
  
  <p>I have the following query :</p>
  
  <pre><code>SELECT  
    S.MODULE, 
    SQL_TEXT , 
    S.EXECUTIONS    
  FROM
    SYS.V_$SQL S, 
    SYS.ALL_USERS U
  WHERE
    S.PARSING_USER_ID=U.USER_ID 
    AND UPPER(U.USERNAME) IN ('USERNAME')
    AND (UPPER(s.MODULE)='APP.EXE')
  ORDER BY S.LAST_LOAD_TIME
  </code></pre>
  
  <p>But if multiple users running the 'APP.EXE' are connected to the same db user, I am not able to understand which OS user executed which query. So I tried to join with V$SESSION view to get the user details.</p>
  
  <pre><code>SELECT  
    S.MODULE,SQL_TEXT ,SN.OSUSER, SN.MACHINE, S.EXECUTIONS
  FROM
    SYS.V_$SQL S, 
    SYS.ALL_USERS U,
    V$SESSION SN 
  WHERE
    S.PARSING_USER_ID=U.USER_ID 
    AND UPPER(U.USERNAME) IN ('USERNAME')
    AND (UPPER(S.MODULE)='APP.EXE')
    AND S.SQL_ID=SN.SQL_ID
  ORDER BY S.LAST_LOAD_TIME
  </code></pre>
  
  <p>But this doesn't seems to be working(In my case it didn't return any rows)
  So, I have the following questions</p>
  
  <p>1) How do I get the queries executed by each session?</p>
  
  <p>2)  The EXECUTIONS column of V_$SQL seems to the executions from all the sessions. How do I know the number of times a particular query is executed by a session?</p>
  
  <p>3) How long a record about a query will be stored in V_$SQL? When do Oracle delete it from the view?</p>
  
  <p>Thanking you all in advance,</p>
  
  <p>Pradeep</p>