Oracle | DBMS

Oracle DBMS_SUPPORT Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located in North America that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Provides an API for tracing that includes access features not available with SQL_TRACE. This package is not part of the default installation and only supports database versions 7.2 through 8.0.5.

Normally I'd leave something like this out of the library but so far Oracle hasn't removed it from RDBMS/ADMIN so it keeps its place in the library.

AUTHID DEFINER

Dependencies

DBMS_STANDARD	V$MYSTAT	V$SESSION
DBMS_SYSTEM

Documented No

First Available 7.2

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