CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_TRACE
Version 11.1
 
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;
 
Related Topics
Autotrace
DBMS_MONITOR
DBMS_SUPPORT
DBMS_SYSTEM
DBMS_TRACE
TKPROF
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [60 users online]    © 2010 psoug.org