Oracle | DBMS

Oracle DBMS_TRACE Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Provides routines for setting/clearing PL/SQL tracing for the session.

AUTHID DEFINER

Constants

Name	Data Type	Value
SQL Trace Constants
trace_all_calls	INTEGER	1
trace_enabled_calls	INTEGER	2
trace_all_exceptions	INTEGER	4
trace_enabled_exceptions	INTEGER	8
trace_limit	INTEGER	16
trace_all_sql	INTEGER	32
trace_enabled_sql	INTEGER	64
trace_all_lines	INTEGER	128
trace_enabled_lines	INTEGER	256
trace_pause	INTEGER	4096
trace_resume	INTEGER	8192
trace_stop	INTEGER	16384
no_trace_administrative	INTEGER	32768
no_trace_handled_exceptions	INTEGER	65536
Version Constants
trace_major_version	BINARY_INTEGER	0
trace_minor_version	BINARY_INTEGER	1
PL/SQL Trace Constants
plsql_trace_start	INTEGER	38
plsql_trace_stop	INTEGER	39
plsql_trace_set_flags	INTEGER	40
plsql_trace_pause	INTEGER	41
plsql_trace_resume	INTEGER	42
plsql_trace_enter_vm	INTEGER	43
plsql_trace_exit_vm	INTEGER	44
plsql_trace_begin_call	INTEGER	45
plsql_trace_elab_spec	INTEGER	46
plsql_trace_elab_body	INTEGER	47
plsql_trace_icd	INTEGER	48
plsql_trace_rpc	INTEGER	49
plsql_trace_end_call	INTEGER	50
plsql_trace_new_line	INTEGER	51
plsql_trace_excp_raised	INTEGER	52
plsql_trace_excp_handled	INTEGER	54
plsql_trace_sql	INTEGER	54
plsql_trace_bind	INTEGER	55
plsql_trace_user	INTEGER	56
plsql_trace_nodebug	INTEGER	57
plsql_trace_excp_unhandled	INTEGER	58

Dependencies

DBMS_TRACE_LIB

Documented Yes

First Available 8.1.5

Pragma PRAGMA SUPPLEMENTAL_LOG_DATA(default, NONE);

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmspbt.sql

Subprograms

CLEAR_PLSQL_TRACE	INTERNAL_VERSION_CHECK	PLSQL_TRACE_VERSION
COMMENT_PLSQL_TRACE	LIMIT_PLSQL_TRACE	RESUME_PLSQL_TRACE
GET_PLSQL_TRACE_LEVEL	PAUSE_PLSQL_TRACE	SET_PLSQL_TRACE
GET_PLSQL_TRACE_RUNNUMBER

Demo Preparations

Enabling Tracing ALTER SESSION SET plsql_debug=TRUE;

or

ALTER [PROCEDURE | FUNCTION | PACKAGE BODY] <unit-name> COMPILE DEBUG;

Call Tracing

Level 1: Trace all calls. This corresponds to the constant trace_all_calls.
Level 2: Trace calls to enabled program units only. This corresponds to the constant trace_enabled_calls.

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

Exception Tracing

Level 1: Trace all exceptions. This corresponds to trace_all_exceptions.
Level 2: Trace exceptions raised in enabled program units only. This corresponds to trace_enabled_exceptions.

Line Tracing

Level 1: Trace all lines. This corresponds to the constant trace_all_lines.
Level 2: Trace lines in enabled program units only. This corresponds to the constant trace_enabled_lines.

SQL Tracing

Level 1: Trace all SQL. This corresponds to the constant trace_all_sql.
Level 2: Trace SQL in enabled program units only. This corresponds to the constant trace_enabled_sql.

Trace Output Table Creation $ORACLE_HOME/rdbms/admin/tracetab.sql desc plsql_trace_runs desc plsql_trace_events

CLEAR_PLSQL_TRACE

Stops trace data dumping in the current session dbms_trace.clear_plsql_trace;

exec dbms_trace.clear_plsql_trace;

      

      PL/SQL procedure successfully completed.

COMMENT_PLSQL_TRACE

Add user comment to the trace table dbms_trace.comment_plsql_trace(comment IN VARCHAR2);

exec dbms_trace.comment_plsql_trace('UW Demo Trace');

      

      PL/SQL procedure successfully completed.

GET_PLSQL_TRACE_LEVEL

Returns the current trace level (a sum of the constants) dbms_trace.get_plsql_trace_level RETURN BINARY_INTEGER;

SELECT dbms_trace.get_plsql_trace_level;

      

      exec dbms_trace.set_plsql_trace(2);

      

      SELECT dbms_trace.get_plsql_trace_level;

GET_PLSQL_TRACE_RUNNUMBER

Return the trace run number dbms_trace.get_plsql_trace_runnumber RETURN BINARY_INTEGER;

SELECT dbms_trace.get_plsql_trace_runnumber;

      

       GET_PLSQL_TRACE_RUNNUMBER

      --------------------------

                               0

INTERNAL_VERSION_CHECK

Verifies version is compatible with current instance dbms_trace.internal_version_check RETURN BINARY_INTEGER;

SELECT dbms_trace.internal_version_check;

      

       INTERNAL_VERSION_CHECK

      -----------------------

                            0

LIMIT_PLSQL_TRACE

Limit the amount of data dumped by the trace (number of records) dbms_trace.limit_plsql_tracelimit IN BINARY_INTEGER := 8192);

exec dbms_trace.limit_plsql_trace(2000);

      

      PL/SQL procedure successfully completed.

PAUSE_PLSQL_TRACE

Pause tracing dbms_trace.pause_plsql_trace;

exec dbms_trace.pause_plsql_trace;

      

      PL/SQL procedure successfully completed.

PLSQL_TRACE_VERSION

Gets the version number of the trace package dbms_trace.plsql_trace_version( major OUT BINARY_INTEGER, minor OUT BINARY_INTEGER);

set serveroutput on

      

      DECLARE

       maj_ver PLS_INTEGER;

       min_ver PLS_INTEGER;

      BEGIN

        dbms_trace.plsql_trace_version(maj_ver, min_ver);

      

        dbms_output.put_line('Major Version is: ' ||

        TO_CHAR(maj_ver) || ' and Minor Version is: ' || TO_CHAR(min_ver));

      END;

      /

RESUME_PLSQL_TRACE

Resume tracing dbms_trace.resume_plsql_trace;

exec dbms_trace.resume_plsql_trace;

      

      PL/SQL procedure successfully completed.

SET_PLSQL_TRACE

dbms_trace.set_plsql_trace(trace_level IN BINARY_INTEGER); dbms_trace.set_plsql_trace(trace_level IN BINARY_INTEGER);

conn uwclass/uwclass@pdbdev

      

      CREATE OR REPLACE FUNCTION getosuser

      RETURN user_users.username%TYPE IS

      

      -- explain use of %TYPE

      vOSUser user_users.username%TYPE;

      

      -- explain INTO and return

      BEGIN

        SELECT osuser

        INTO vOSUser

        FROM gv$session

        WHERE sid = (

          SELECT sid

          FROM  v$mystat

          WHERE rownum = 1);

      

        RETURN vOSUser;

      EXCEPTION

        WHEN OTHERS THEN

          RETURN 'UNK';

      END getosuser;

      /

      

      ALTER FUNCTION getosuser COMPILE DEBUG;

      

      set serveroutput on

      

      DECLARE

       x VARCHAR2(30);

      BEGIN

        dbms_trace.set_plsql_trace(1);

        SELECT getosuser

        INTO x

        FROM dual;

      

        dbms_output.put_line(x);

      

        dbms_trace.pause_plsql_trace;

      END;

      /

      

      conn sys@pdbdev as sysdba

      

      SELECT runid, run_date, run_owner

      FROM plsql_trace_runs;

      

      set linesize 121

      col event_proc_name format a20

      col module format a20

      

      SELECT event_seq, stack_depth, module, proc_unit, proc_line

      FROM plsql_trace_events;

      

      SELECT module

      FROM plsql_trace_events;