Oracle DBMS_SUPPORT Demos

Oracle DBMS_SUPPORT Version 11.2.0.3

General Information

Purpose

Provides an API for tracing that includes access features not available with SQL_TRACE. This package is not part of the default installation.

AUTHID

DEFINER

Dependencies

DBMS_SYSTEM

V$MYSTAT

V$SESSION

First Available

8.0.6

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

Owned by SYS with no privileges granted

Source

{$ORACLE_HOME}/rdbms/admin/dbmssupp.sql

Subprograms

MYSID	START_TRACE	STOP_TRACE
PACKAGE_VERSION	START_TRACE_IN_SESSION	STOP_TRACE_IN_SESSION

MYSID

Return Session SID

dbms_support.mysid RETURN NUMBER;

SELECT sid FROM v$mystat WHERE rownum = 1;

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 the 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);

dbms_lock.sleep(100);

dbms_support.stop_trace_in_session(s, r);
END;
/

STOP_TRACE

Stops tracing

dbms_support.stop_trace;

exec dbms_support.stop_trace;

STOP_TRACE_IN_SESSION

Stops tracing within a specified session

dbms_support.stop_trace_in_session(id IN NUMBER, serial IN NUMBER);

See START_TRACE_IN_SESSION Demo Above