The trace files produced by DBMS_TRACE can get really big. You can focus the output by enabling only specific programs for trace data collection. Note that you cannot use this approach with remote procedure calls.

To enable a specific program for tracing, you can alter the session to enable any programs that are created or replaced in the session. To take this approach, issue this command:

  ALTER SESSION SET PLSQL_DEBUG=TRUE;

If you don’t want to alter your entire session, you can recompile a specific program unit in debug mode as follows (not applicable to anonymous blocks):

  ALTER [PROCEDURE | FUNCTION | PACKAGE BODY] program_name COMPILE DEBUG;

After you have enabled the programs in which you’re interested, the following call will initiate tracing just for those program units:

  DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_enabled_calls);

You can also restrict the trace information to only those exceptions raised within enabled programs with this call:

  DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_enabled_exceptions);

If you request tracing for all programs or exceptions and also request tracing only for enabled programs or exceptions, the request for “all” takes precedence.

Pausing and resuming the trace process

The SET_PLSQL_TRACE procedure can do more than just determine which information will be traced. You can also request that the tracing process be paused and resumed. The following statement, for example, requests that no information be gathered until tracing is resumed:

  DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_pause);

DBMS_TRACE will write a record to the trace file to show when tracing was paused and/or resumed.

Use the DBMS_TRACE.trace_limit constant to request that only the last 8,192 trace events of a run be preserved. This approach helps ensure that you can turn tracing on without overwhelming the database with trace activity. When the trace session ends, only the last 8,192 records are saved.

Format of collected data

If you request tracing only for enabled program units and the current program unit is not enabled, no trace data is written. If the current program unit is enabled, call tracing writes out the program unit type, name, and stack depth.

Exception tracing writes out the line number. Raising an exception records trace information on whether the exception is user-defined or predefined, and records the exception number in the case of predefined exceptions. If you raise a user-defined exception, you will always see an error code of 1.

Here is an example of the output from a trace of the showemps procedure:

  *** 1999.06.14.09.59.25.394

  *** SESSION ID:(9.7) 1999.06.14.09.59.25.344
  ------------ PL/SQL TRACE INFORMATION ----   Levels set : 1

  Trace:  ANONYMOUS BLOCK: Stack depth = 1
  Trace:   PROCEDURE SCOTT.SHOWEMPS: Call to entry at line 5 Stack depth = 2
  Trace:    PACKAGE BODY SYS.DBMS_SQL: Call to entry at line 1 Stack depth = 3
  Trace:     PACKAGE BODY SYS.DBMS_SYS_SQL: Call to entry at line 1 Stack depth = 4
  Trace:     PACKAGE BODY SYS.DBMS_SYS_SQL: ICD vector index = 21 Stack depth = 4
  Trace:    PACKAGE PLVPRO.P: Call to entry at line 26 Stack depth = 3
  Trace:   PACKAGE PLVPRO.P: ICD vector index = 6 Stack depth = 3
  Trace:   PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 3
  Trace:   PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 3
  Trace:    PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 4