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