Oracle DBMS_TRACE Demos

Oracle DBMS_TRACE Version 11.2.0.3

General Information

Purpose

Note: This package provides routines for setting/clearing PL/SQL tracing for the session.

AUTHID

DEFINER

Constants

Name	Data Type	Value
trace_all_calls	INTEGER	1
trace_enabled_calls	INTEGER	2
trace_all_exceptions	INTEGER	4
trace_enabled_exceptions	INTEGER	8
trace_all_sql	INTEGER	32
trace_enabled_sql	INTEGER	64
trace_all_lines	INTEGER	128
trace_enabled_lines	INTEGER	256
trace_stop	INTEGER	16384
trace_pause	INTEGER	4096
trace_resume	INTEGER	8192
trace_limit	INTEGER	16
no_trace_administrative	INTEGER	32768
no_trace_handled_exceptions	INTEGER	65536
trace_major_version	BINARY_INTEGER	0
trace_minor_version	BINARY_INTEGER	1
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	53
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

First Available

8.1.5

Security Model

Owned by SYS: Execute is 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_RUNNUMBER	PAUSE_PLSQL_TRACE	SET_PLSQL_TRACE

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

conn uwclass/uwclass

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