dbms_trace in Oracle

• dbms_trace provides subprograms to start and stop PL/SQL tracing in a session. The trace data is collected as the program executes, and it is written out to data dictionary tables.

• set_plsql_trace: This procedure starts tracing data dumping in a session. Provide the trace level at which you want your PL/SQL code traced as an IN parameter.

• clear_plsql_trace: This procedure stops trace data dumping in a session.

• plsql_trace_version: This procedure returns the version number of the trace package as an OUT parameter

• A typical trace session involves:
• Enabling specific subprograms for trace data collection. This is optional.
• Starting the PL/SQL tracing session dbms_trace.set_plsql_trace
• Running the application that is to be traced
• Stopping the PL/SQL tracing session dbms_trace.clear_plsql_trace

• Specifying a Trace Level: During the trace session, there are 2 levels that you can specify to trace calls, exceptions, SQL, and lines of code.
• Trace Calls
• Level 1: Trace all calls. This corresponds to the constant trace_all_calls.
• Level 2: Trace calls only to enabled program units. This corresponds to the constant trace_enabled_calls.

• Trace Exceptions:
• Level 1: Trace all exceptions. This corresponds to trace_all_exceptions.
• Level 2: Trace exceptions raised only in enabled program units. This corresponds to trace_enabled_exceptions.

• Trace SQL:
• Level 1: Trace all SQL. This corresponds to the constant trace_all_sql.
• Level 2: Trace SQL in only enabled program units. This corresponds to the constant trace_enabled_sql.

• Trace Lines:
• Level 1: Trace all lines. This corresponds to the constant trace_all_lines.
• Level 2: Trace lines only in enabled program units. This corresponds to the constant trace_enabled_lines.

• Steps to Trace PL/SQL Code:
• There are five steps to trace PL/SQL code using the dbms_trace package:
• Enable specific program units for trace data collection.
• Use dbms_trace.set_plsql_trace to identify a trace level.
• Run your PL/SQL code.
• Use dbms_trace.clear_plsql_trace to stop tracing data.
• Read and interpret the trace information.