Trace Session for Schema on Login

The following trigger starts a session trace whenever a session for a particular schema logs into the database:

CREATE OR REPLACE TRIGGER ON_MY_SCHEMA_LOGIN
AFTER LOGON ON DATABASE
WHEN ( USER = 'MySCHEMA' )
BEGIN
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

Level definitions:

For methods that require tracing levels, the following are valid values:

  • 0 – No trace. Like switching sql_trace off.

  • 2 – The equivalent of regular sql_trace.

  • 4 – The same as 2, but with the addition of bind variable values.

  • 8 – The same as 2, but with the addition of wait events.

  • 12 – The same as 2, but with both bind variable values and wait events.

  • To turn off session level traces that are running:

    ALTER SESSION SET EVENTS '10046 trace name context off';
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: