ODP.NET – Setting DB Trace with a little twist

Just wanted to share this small thingy. When you are setting up your DB Trace for DB gurus to read (I found in Oracle only real DB sorcerers can decode this) you might make your life easier a bit, if in addition to well known:

"ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'"; – for switching on
"ALTER SESSION SET EVENTS '10046 trace name context off'"; – for switching off

you add “ALTER SESSION SET tracefile_identifier” to give a name to a trace file and relate it to your activity in this way.

Whole code would look like:

   1: public void SwitchDBTrace(DBTraceState targetState, OracleConnection connection)

   2: {

   3:     string commandText = (targetState == DBTraceState.Off) ? switchDBTraceOffCommand : switchDBTraceOnCommand;

   4:  

   5:     try

   6:     {

   7:  

   8:         Log.TraceData(Log.Source, TraceEventType.Information, 0, String.Format("Switching DB Trace to [{0}].", targetState));

   9:  

  10:         using (OracleCommand perfCmd = new OracleCommand(commandText, connection))

  11:         {

  12:             perfCmd.ExecuteNonQuery();

  13:  

  14:             if (targetState == DBTraceState.On)

  15:             {

  16:                 perfCmd.CommandText = String.Format("ALTER SESSION SET tracefile_identifier = '{0}'", Trace.CorrelationManager.ActivityId.ToString().Replace("-","_"));

  17:                 perfCmd.ExecuteNonQuery();

  18:             }

  19:         }

  20:  

  21:         Log.TraceData(Log.Source, TraceEventType.Information, 0, String.Format("DB Trace switched to [{0}].", targetState));

  22:     }

  23:     catch (Exception ex)

  24:     {

  25:         Log.TraceData(Log.Source, TraceEventType.Error, 0, String.Format("There was an error when trying to switch the DB trace with command [{0}]: Exception: {1}.", commandText, ex));

  26:     }

  27: }

with helpers:

   1: private string switchDBTraceOnCommand = "ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'";

   2: private string switchDBTraceOffCommand = "ALTER SESSION SET EVENTS '10046 trace name context off'";

   3:  

   4: public enum DBTraceState

   5: {

   6:     None = 0,

   7:     Off = None,

   8:     On = 1

   9: }

This would give you file name like: xyz_ora_1142900_40a2e9ef_be21_4a0c_9a5e_aa70dfb8e423, that is quite better traceable back into your activity.

[14-Apr-2011] Small update, here is how the db trace setup looks in the pl-sql itself if you ever need it:

   1: declare

   2:   -- declare params

   3:  

   4: begin

   5:   -- set params

   6:  

   7:   execute immediate('ALTER SESSION SET EVENTS ''10046 trace name context forever, level 12''');

   8:   

   9:  

  10:   -- Call the procedure

  11:  

  12:                              

  13:  execute immediate('ALTER SESSION SET EVENTS ''10046 trace name context off''');

  14:  

  15: end;