General Information |
Note: This package provides routines for setting/clearing PL/SQL tracing for the session. |
Source |
{ORACLE_HOME}/rdbms/admin/dbmspbt.sql |
First Available |
8.1.5 |
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 |
Enabling Tracing |
ALTER SESSION SET plsql_debug=TRUE;
or
ALTER [PROCEDURE | FUNCTION | PACKAGE BODY] <unit-name>
COMPILE DEBUG; |
Tracing Calls |
- 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.
|
Tracing Exceptions |
- 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.
|
Tracing Lines |
- 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.
|
Tracing SQL |
- 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.
|
Create Trace Output Table |
$ORACLE_HOME/rdbms/admin/tracetab.sql
desc plsql_trace_runs
desc plsql_trace_events |
Security Model |
Execute is granted to PUBLIC
|
|
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 |
Starts tracing in the current session |
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 gv$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;
|