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

Related Topics
Autotrace
Built-in Functions
Built-in Packages
DBMS_MONITOR
DBMS_SUPPORT
DBMS_SYSTEM
TKPROF
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx