7.2.1 Installing DBMS_TRACE

This package may not have been installed automatically with the rest of the built-in packages. To determine whether DBMS_TRACE is present, connect to SYS and execute this command:

 BEGIN DBMS_TRACE.CLEAR_PLSQL_TRACE; END; /

If you see this error:

PLS-00201: identifier 'DBMS_TRACE.CLEAR_PLSQL_TRACE'             must be declared

then you must install the package. To do this, remain connected as SYS and run the following files in the order specified:

\Oracle\Ora81\Rdbms\Admin\dbmspbt.sql

\Oracle\Ora81\Rdbms\Admin\prvtpbt.plb

TIP: The directory shown here is the default for a Windows NT installation. Your Oracle 8.1 home directory may be different, but these files will always be found in the Rdbms\Admin subdirectory under the Oracle 8.1 home directory.

7.2.2 DBMS_TRACE Programs

The programs in the DBMS_TRACE package are listed in Table 7.5 .

Table 7.5: DBMS_TRACE Programs

Program	Description
SET_PLSQL_TRACE procedure	Starts PL/SQL tracing in the current session
CLEAR_PLSQL_TRACE procedure	Stops the dumping of trace data for that session
PLSQL_TRACE_VERSION procedure	Gets the major and minor version numbers of the DBMS_TRACE package

To trace execution of your PL/SQL code, you must first start the trace with a call to:

DBMS_TRACE.SET_PLSQL_TRACE (
trace_level
 INTEGER); 

in your current session, where trace_level is one of the following values:

DBMS_TRACE.trace_all_calls          CONSTANT INTEGER := 1;  DBMS_TRACE.trace_enabled_calls      CONSTANT INTEGER := 2;  DBMS_TRACE.trace_all_exceptions     CONSTANT INTEGER := 4;  DBMS_TRACE.trace_enabled_exceptions CONSTANT INTEGER := 8; 

To turn on tracing from all programs executed in your session, issue this call:

DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_all_calls);

To turn on tracing for all exceptions raised during the session, issue this call:

DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_all_exceptions);

You then run your code; when you are done, you stop the trace session by calling:

DBMS_TRACE.CLEAR_PLSQL_TRACE; 

You can then examine the contents of the trace file. The names of these files are generated by Oracle; you will mostly need to pay attention to the modification date of the files to figure out which file to examine. The location of the trace files is discussed later in Section 7.2.4, "Format of Collected Data . You cannot use PL/SQL tracing with the multithreaded server (MTS).

7.2.3 Controlling Trace File Contents

The trace files produced by DBMS_TRACE can get really big. You can minimize the trace output and focus it by obtaining trace information only for specific programs that you have enabled for trace data collection.

TIP: 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.

7.2.4 Format of Collected Data

If you request tracing only for enabled program units and the current program unit is not enabled, then no trace data is written. If the current program unit is enabled for tracing, then call tracing writes out the program unit type, name, and stack depth. If the current program unit is not enabled, then call tracing writes out the program unit type, line number, 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.

In Oracle8 i under Windows NT, the trace files are written to the following directory (by default):

Oracle\Admin\Oracle81\udump

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

*** 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