Home arrow Oracle arrow Page 3 - Debugging PL/SQL Code

Controlling trace file contents - Oracle

In this eighth part of a nine-part series on managing PL/SQL code, you will learn some effective debugging strategies. We'll also begin talking about tuning PL/SQL programs. This artice is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O'Reilly; ISBN: 0596009771). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

  1. Debugging PL/SQL Code
  2. Tracing Execution of Your Code
  3. Controlling trace
  4. Tuning PL/SQL Programs
By: O'Reilly Media
Rating: starstarstarstarstar / 17
December 13, 2007

print this article



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:


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):


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 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.
  *** SESSION ID:(9.7) 1999.
  ------------ 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

>>> More Oracle Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: