Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to see a list of all past SQL statements executed by a specific session (or number of sessions)?

For example, the following query gives me all currently executing statements from all sessions where the machine is VZWCLIENT8. But I would like to see the past execution.

select s.sid, s.serial#, a.sql_text
from v$session s
join v$sqlarea a on a.hash_value = s.sql_hash_value
where upper(machine) like 'VZWCLIENT8'
share|improve this question

migrated from stackoverflow.com Nov 21 '12 at 22:52

This question came from our site for professional and enthusiast programmers.

3 Answers 3

yes, read up on FGA (fine grained auditing).

http://www.oracle-base.com/articles/10g/auditing-10gr2.php

this should do what you want to accomplish.

share|improve this answer
    
Thanks for the answer, but this seems something I would enable on specific objects to be audited. I'd like a trail of all statements -- something akin to looking at the NET browser in Firebug, or perhaps a monitoring proxy logger. –  Achille Nov 21 '12 at 18:49
    
If you want all statements, that's even better. You don't need fine grained auditing then, just the old fashioned (basic) auditing. This is very easy. And this is exactly the tool for the job. –  kubanczyk Nov 22 '12 at 21:27

I don't think Oracle keeps track of past closed queries.

However, you can find out what cursors a session has opened with V$OPEN_CURSOR. Since many applications cache the cursors for later reuse (this is automatic in PL/SQL: a cursor won't be completely discarded unless you reach the maximum number of open cursors), in many cases all past queries will be in this view:

SELECT t.*
  FROM v$open_cursor c
  JOIN v$sqltext t ON c.hash_value = t.hash_value
                  AND c.address = t.address
 WHERE sid = :sid
 ORDER BY t.hash_value, t.piece
share|improve this answer

You can get some information from cached cursors through the V$SQL view, but to really look at all the queries of a given session, you will need to enable tracing for that session.

This can be accomplished from within that session with ALTER SESSION SET sql_trace = true; or from a DBA session using the PL/SQL package DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION

There's a nice walk through over at orafaq

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.