Oracle 12c DBMS

Oracle DBMS_SUPPORT Version 12.1.0.2

General Information

Library Note

Morgan's Library Page Header

The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Also important ... there are rumors on the web about SPARC and Solaris being discontinued. Welcome to the age of fake news. There is absolutely not a byte of truth to these irresponsible rumors.

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