Oracle DBMS_TRACE Built-In Package
Versions 8.1.5 - 21c

Security Advisory
SQL Tracing is valuable tool when used for performance tuning and triage. Tracing can also be used to learn about the internals of wrapped code and to reveal methods and dependencies. This PL/SQL package is still documented and supported and has EXECUTE granted to PUBLIC which makes it a substantial security risk.
 
Recommended Security Rules

 NEVER
  • Deploy a database without revoking EXECUTE from PUBLIC.
 WITH GREAT CARE
  • Identify legitimate requirements for access to this package and grant EXECUTE explicitly to only justified schemas
  • Query the data dictionary after EXECUTE has been revoked from PUBLIC to verify the equivalence created is the equivalence approved by IT management and your CISO
 CAUTIONS
  • Some usage may be in the form of dynamic SQL so carefully verify usage requirements in source code as well as in DBA_DEPENDENCIES
 
How Oracle Works
SQL Tracing with ALTER SYSTEM has been around since the Jurassic. Oracle has been building tools to make tracing easier since almost that long. ALTER SYSTEM statements can be manually entered at the SQL*Plus command line and will produce an output. There are hundreds, if not thousands, of Event Numbers that can be used to trace specific behaviors but the overwhelming majority of tracing is with the 10046 event and that this package was designed to simplify starting and stopping 10046 tracing. This package, through its constants, greatly extends the capabilities first introduced with the DBMS_SUPPORT package and can provide little-known but valuable insights into SQL and PL/SQL execution.

It is the value that this package can provide, when put into the wrong hands through a grant of EXECUTE to PUBLIC that creates the issue. Use of this package should be tightly restricted to only those that can justify a need and usage should be audited.
 
DBMS_SUPPORT Information
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
DBMS_MONITOR
DBMS_SUPPORT
DBMS_SYSTEM
TKPROF