Oracle DBMS_TRACE
Version 18.1.0.0

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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
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;
 
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;

Related Topics
Autotrace
Built-in Functions
Built-in Packages
DBMS_MONITOR
DBMS_SUPPORT
DBMS_SYSTEM
TKPROF
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved