Oracle | DBMS

Oracle DBMS_TRACE Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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;

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');

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

      FROM dual;

      

      exec dbms_trace.set_plsql_trace(2);

      

      SELECT dbms_trace.get_plsql_trace_level

      FROM dual;

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

      FROM dual;

INTERNAL_VERSION_CHECK

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

SELECT dbms_trace.internal_version_check

      FROM dual;

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

PAUSE_PLSQL_TRACE

Pause tracing dbms_trace.pause_plsql_trace;

exec dbms_trace.pause_plsql_trace;

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;

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;