How to trace with binds a query which runs in seconds?

The challenge here is that we have an application which connects to the database, runs a very fast query ( a mater of seconds) and then they disconnect. How are we going not only to trace these queries, but also to catch the bind variables used?

The answer is, we'll use a logon trigger and "alter session set event".
Please see an example of such trigger below:




SQL> get cre_logon_trigger.sql
  1  -- To be run as sys ***** --
  2  create or replace trigger apptest_logon after logon on database
  3  begin
  4  if user='APPTEST'
  5  then
  6  execute immediate 'alter session set tracefile_identifier = ''apptest''';
  7  ----execute immediate 'alter session set sql_trace=true';
  8  execute immediate 'alter session set events ''10046 trace name context forever, level 4''';
  9  end if;
 10* end;

A few remarks:

Line #6: we are identifying the queries by the DB user used, apptest.
Line #8: we use level 4 for 10046 event, which means to trace bind variables.
Line  #7: commented out, usually used to trace when simple trace files is required, no binds.