| General Information |
| Library Note |
Morgan's Library Page Header
|
| 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;
PL/SQL procedure successfully completed. |
| |
| 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');
PL/SQL procedure successfully completed. |
| |
| 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;
exec dbms_trace.set_plsql_trace(2);
SELECT dbms_trace.get_plsql_trace_level; |
| |
| 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;
GET_PLSQL_TRACE_RUNNUMBER
--------------------------
0 |
| |
| INTERNAL_VERSION_CHECK |
| Verifies version is compatible with current instance |
dbms_trace.internal_version_check RETURN BINARY_INTEGER; |
SELECT dbms_trace.internal_version_check;
INTERNAL_VERSION_CHECK
-----------------------
0 |
| |
| 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);
PL/SQL procedure successfully completed. |
| |
| PAUSE_PLSQL_TRACE |
| Pause tracing |
dbms_trace.pause_plsql_trace; |
exec dbms_trace.pause_plsql_trace;
PL/SQL procedure successfully completed. |
| |
| 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;
PL/SQL procedure successfully completed. |
| |
| 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;
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; |