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_SUPPORT
Version 11.1
 
General
Note: Provides an API for tracing that includes access features not available with SQL_TRACE.
Source $ORACLE_HOME/rdbms/admin/dbmssupp.sql
First Available 8.0.6
Dependencies
DBMS_SYSTEM GV_$MYSTAT GV_$SESSION
Installation conn / as sysdba

@?rdbms\admin\dbmssupp.sql

GRANT execute ON dbms_support TO uwclass;

CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;
Security Model No privileges granted
 
MYSID
Return Session SID dbms_support.mysid RETURN NUMBER;
SELECT DISTINCT sid FROM gv$mystat;

SELECT SYS_CONTEXT('USERENV', 'SID') FROM dual;

SELECT dbms_support.mysid FROM dual;
 
PACKAGE_VERSION
Returns the minimum Oracle version of PL/SQL supported dbms_support.package_version RETURN VARCHAR2;
SELECT dbms_support.package_version
FROM dual;
 
START_TRACE

Starts Tracing In The Current Session
dbms_support.start_trace(
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE);
DECLARE
 w BOOLEAN := TRUE;
 b BOOLEAN := TRUE;
BEGIN
  dbms_support.start_trace(w,b);
END;
/
 
START_TRACE_IN_SESSION

Starts Tracing In A Specified Session
dbms_support.start_trace_in_session (
sid    IN NUMBER,
serial  IN NUMBER,
waits  IN BOOLEAN DEFAULT TRUE,
binds  IN BOOLEAN DEFAULT FALSE);
DECLARE
 s gv$session.sid%TYPE;
 r gv$session.serial#%TYPE;
 w BOOLEAN := TRUE;
 b BOOLEAN := TRUE;
BEGIN
  SELECT sid, serial#
  INTO s, r
  FROM gv$session
  WHERE username = 'UWCLASS';

  dbms_support.start_trace_in_session(s, r, w, b);
END;
/
 
STOP_TRACE
Stops Tracing dbms_support.stop_trace;
exec dbms_support.stop_trace;
 
STOP_TRACE_IN_SESSION

Stops Tracing
dbms_support.stop_trace_in_session(id IN NUMBER, serial IN NUMBER);
DECLARE
 s gv$session.sid%TYPE;
BEGIN
  SELECT sid
  INTO s
  FROM gv$session
  WHERE username = 'UWCLASS';

  dbms_support.stop_trace_in_session(s, NULL);
END;
/
 
Related Topics
DBMS_MONITOR
DBMS_SYSTEM
Trace & TKPROF
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [266 users online]    © 2010 psoug.org