General Information
Library Note
Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx .
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;