Enable 10046 trace in a session

There are a number of ways to trace a session:

1. Enable trace at the beginning of your session
2. Enable trace of a running session

1. Enable tracing at the begin of your session

To enable tracing for a particular session we will need to set the Oracle event 10046.
This event contains a level which can be combined using a logical OR relation to mix different trace functions.

This is what the different levels are used for:

1	Enable statistics for parse, execute, fetch, commit and rollback operations. This is the same as doing a 'alter session set sql_trace=true' SQL call.
2	No information available
4	Enable tracing of bind variables
8	Enable tracing for wait events. These should match the events as found in v$event_name

As said before we can combine the different levels to enable combination's of tracing.

A level of 12 means that we will trace bind variables as well as wait events. When setting the level to a value other than zero, level 1 will also be activated. So setting the level to 12 also enables tracing for parse, execute, fetch,... operations.

-- To enable tracing
alter session set events '10046 trace name context forever, level 12'

--To stop tracing of the current session
alter session set events '10046 trace name context off'

Enable trace of a running session

In order to enable tracing for a running application, one can use the dbms_system package.

-- to enable tracing for another sid, serial# pair
sys.dbms_system.set_ev( sid, serial#, 10046, 12, '');
-- to disable tracing.
sys.dbms_system.set_ev( sid, serial#, 10046, 0, '');

Alternatively, to just enable a level 1 trace:

-- to enable tracing for another sid, serial# pair
sys.dbms_system.set_sql_trace_in_session(sid, serial#, true);
-- to disable tracing.
sys.dbms_system.set_sql_trace_in_session(sid, serial#, false);

When your application require frequent tracing for a particular setting, one might think about including an option into the application which - when enabled - just starts tracing the session.

Alternatively, you can create a schema logon trigger which enables tracing when a user logs on to the schema.