Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)

Part Number B14258-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

110 DBMS_TRACE

The DBMS_TRACE package contains the interface to trace PL/SQL functions, procedures, and exceptions.

This chapter contains the following topics:


Using DBMS_TRACE


Overview

DBMS_TRACE provides subprograms to start and stop PL/SQL tracing in a session. Oracle collects the trace data as the program executes and writes it to database tables.

A typical session involves:


Security Model

This package must be created under SYS.


Constants

DBMS_TRACE uses these constants:

trace_all_calls          constant INTEGER := 1;
trace_enabled_calls      constant INTEGER := 2;
trace_all_exceptions     constant INTEGER := 4;
trace_enabled_exceptions constant INTEGER := 8;
trace_all_sql            constant INTEGER := 32;
trace_enabled_sql        constant INTEGER := 64;
trace_all_lines          constant INTEGER := 128;
trace_enabled_lines      constant INTEGER := 256;
trace_stop               constant INTEGER := 16384;
trace_pause              constant INTEGER := 4096;
trace_resume             constant INTEGER := 8192;
trace_limit              constant INTEGER := 16;
trace_major_version      constant BINARY_INTEGER := 1;
trace_minor_version      constant BINARY_INTEGER := 0;

Oracle recommends using the symbolic form for all these constants.


Restrictions

You cannot use PL/SQL tracing in a shared server environment.


Operational Notes

Controlling Data Volume

Profiling large applications may produce a large volume of data. You can control the volume of data collected by enabling specific program units for trace data collection.

You can enable a program unit by compiling it debug. This can be done in one of two ways:

alter session set plsql_debug=true; 
create or replace ... /* create the library units - debug information will be generated */ 

or:

/* recompile specific library unit with debug option */ 
alter [PROCEDURE | FUNCTION | PACKAGE BODY]  <libunit-name> compile debug; 

Note:

You cannot use the second method for anonymous blocks.

You can limit the amount of storage used in the database by retaining only the most recent 8,192 records (approximately) by including TRACE_LIMIT in the TRACE_LEVEL parameter of the SET_PLSQL_TRACE procedure.

Creating Database Tables to Collect DBMS_TRACE Output

You must create database tables into which the DBMS_TRACE package writes output. Otherwise, the data is not collected. To create these tables, run the script TRACETAB.SQL. The tables this script creates are owned by SYS.

Collecting Trace Data

The PL/SQL features you can trace are described in the script DBMSPBT.SQL. Some of the key tracing features are:

Additional features of DBMS_TRACE also allow pausing and resuming trace, and limiting the output.

Tracing Calls

Two levels of call tracing are available:

Enabling cannot be detected for remote procedure calls (RPCs); hence, RPCs are only traced with level 1.


Tracing Exceptions

Two levels of exception tracing are available:


Tracing SQL

Two levels of SQL tracing are available:


Tracing Lines

Two levels of line tracing are available:

When tracing lines, Oracle adds a record to the database each time the line number changes. This includes line number changes due to procedure calls and returns.

Note:

For both all types of tracing, level 1 overrides level 2. For example, if both level 1 and level 2 are enabled, then level 1 takes precedence.

Collected Data

If tracing is requested only for enabled program units, and if the current program unit is not enabled, then no trace data is written.

When tracing calls, both the call and return are traced. The check for whether tracing is "enabled" passes if either the called routine or the calling routine is "enabled".

Call tracing will always output the program unit type, program unit name, and line number for both the caller and the callee. It will output the caller's stack depth. If the caller's unit is enabled, the calling procedure name will also be output. If the callee's unit is enabled, the called procedure name will be output

Exception tracing writes out the line number. Raising the exception shows information on whether the exception is user-defined or pre-defined. It also shows the exception number in the case of pre-defined exceptions. Both the place where the exceptions are raised and their handler is traced. The check for tracing being "enabled" is done independently for the place where the exception is raised and the place where the exception is handled.

All calls to DBMS_TRACE.SET_PLSQL_TRACE and DBMS_TRACE.CLEAR_PLSQL_TRACE place a special trace record in the database. Therefore, it is always possible to determine when trace settings were changed.

Trace Control

As well as determining which items are collected, you can pause and resume the trace process. No information is gathered between the time that tracing is paused and the time that it is resumed. The constants TRACE_PAUSE and TRACE_RESUME are used to accomplish this. Trace records are generated to indicate that the trace was paused/resumed.

It is also possible to retain only the last 8,192 trace events of a run by using the constant TRACE_LIMIT. This allows tracing to be turned on without filling up the database. When tracing stops, the last 8,192 records are saved. The limit is approximate, since it is not checked on every trace record. At least the requested number of trace records will be generated; up to 1,000 additional records may be generated.


Summary of DBMS_TRACE Subprograms

Table 110-1 DBMS_TRACE Package Subprograms

Subprogram Description
CLEAR_PLSQL_TRACE Procedure
Stops trace data dumping in session
PLSQL_TRACE_VERSION Procedure
Gets the version number of the trace package
SET_PLSQL_TRACE Procedure
Starts tracing in the current session


CLEAR_PLSQL_TRACE Procedure

This procedure disables trace data collection.

Syntax

DBMS_TRACE.CLEAR_PLSQL_TRACE; 

PLSQL_TRACE_VERSION Procedure

This procedure gets the version number of the trace package. It returns the major and minor version number of the DBMS_TRACE package.

Syntax

DBMS_TRACE.PLSQL_TRACE_VERSION ( 
   major OUT BINARY_INTEGER, 
   minor OUT BINARY_INTEGER); 

Parameters

Table 110-2 PLSQL_TRACE_VERSION Procedure Parameters

Parameter Description
major Major version number of DBMS_TRACE.
minor Minor version number of DBMS_TRACE.


SET_PLSQL_TRACE Procedure

This procedure enables PL/SQL trace data collection.

Syntax

DBMS_TRACE.SET_PLSQL_TRACE ( 
   trace_level INTEGER); 

Parameters

Table 110-3 SET_PLSQL_TRACE Procedure Parameters

Parameter Description
trace_level You must supply one or more of the constants as listed. By summing the constants, you can enable tracing of multiple PL/SQL language features simultaneously. The control constants "trace_pause", "trace_resume" and "trace_stop" should not be used in combination with other constants

Also see "Collecting Trace Data"  for more information.