6

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'

3 Answers 3

2

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.

2
  • 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, 2012 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, 2012 at 21:27
1

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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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