Oracle Built-in Packages

DBMS_SYSTEM: Setting Events for Debugging

The DBMS_SYSTEM package contains procedures for setting special internal trace events that can help the DBA or Oracle Technical Support personnel diagnose and debug serious database problems.

The procedures in DBMS_SYSTEM are used by DBAs under special circumstances and should not be used by end users or coded into applications. Most of the procedures should be used only under specific instructions from Oracle Technical Support, as improper usage can actually crash or damage the database. The extremely useful SET_SQL_TRACE_IN_SESSION procedure, however, has less serious implications. Both DBAs and developers should be aware of and know how to use it.

Getting Started with DBMS_SYSTEM

The DBMS_SYSTEM package is created when the Oracle database is installed. The dbmsutil.sql script (found in the built-in packages source code directory, as described in Chapter 1) contains the source code for this package’s specification. This script is called by catproc.sql, which is normally run immediately after database creation.

Unlike the other packages created by the dbmsutil.sql script, no public synonym for DBMS_SYSTEM is created, and no privileges on the package are granted. Thus, only the SYS user can normally reference and make use of this package. Other users (or roles) can be granted access to DBMS_SYSTEM by having the SYS user issue the following SQL command:

GRANT EXECUTE ON DBMS_SYSTEM TO username;

In practice, it is probably better and safer to ...