Tracing PL/SQL programs using DBMS_TRACE

Tracing program execution is an important exercise in a scrum based database development environment. Sometimes, in a modular programming model, it becomes difficult to track the program execution path. Oracle provides the DBMS_TRACE package to trace PL/SQL program code.

DBMS_TRACE is an Oracle supplied package that can be used to enable and disable tracing in database sessions. The program execution path is traced when you execute a PL/SQL program in a trace-enabled session. The trace information is captured and stored in database tables. These trace tables can be further analyzed to examine the execution path of a PL/SQL program.

The following figure shows the steps involved in PL/SQL code tracing:

Note

PL/SQL code tracing cannot be done in a shared server environment

Installing the DBMS_TRACE package

You can install the DBMS_TRACE package by running the following scripts from the $ORACLE_HOME/rdbms/admin folder:

• dbmspbt.sql: This script creates the DBMS_TRACE...