Capture all SQL run between two points in time

There are situations where it is useful to capture the SQL
that a particular user is running in the database. Usually
you would simply enable session tracing for that user, but
there are two potential problems with that approach.

The first is that many web based applications maintain a
pool of persistent database connections which are shared
amongst multiple users. The second is that some applications
connect, run some SQL and disconnect very quickly, making it
tricky to enable session tracing at all (you could of course
use a logon trigger to enable session tracing in this case).

A quick and dirty solution to the problem is to capture all
SQL statements that are run between two points in time.

The following procedure will create two tables, each containing
a snapshot of the database at a particular point. The tables
will then be queried to produce a list of all SQL run during
that period.

If possible, you should do this on a quiet development system -
otherwise you risk getting way too much data back.

1. Take the first snapshot
Run the following sql to create the first snapshot:

create table sql_exec_before as
select executions
, hash_value
from v$sqlarea
/

2. Get the user to perform their task within the application

3. Take the second snapshot

select aft.hash_value
from sql_exec_before bef
, sql_exec_after aft
where aft.executions > bef.executions
and aft.hash_value = bef.hash_value (+)
/

4. Check the results
Now that you have captured the SQL it is time to query the results.

This first query will list all query hashes that have been executed:
select aft.hash_value
from sql_exec_before bef
, sql_exec_after aft
where aft.executions > bef.executions
and aft.hash_value = bef.hash_value (+)
/

This one will display the hash and the SQL itself:

set pages 999 lines 100
break on hash_value
select hash_value
, sql_text
from v$sqltext
where hash_value in (
select aft.hash_value
from sql_exec_before bef
, sql_exec_after aft
where aft.executions > bef.executions
and aft.hash_value = bef.hash_value (+)
)
order by
hash_value
, piece
/

5. Tidy up

Don't forget to remove the snapshot tables once you've finished:

drop table sql_exec_before
/

drop table sql_exec_after
/


Source : www.shutdownabort.com