The DBMS_SUPPORT Package (Doc ID 62294.1)

The DBMS_SUPPORT Package
~~~~~~~~~~~~~~~~~~~~~~~~
This package is an add on to Oracle releases 7.2 onwards to provide a
simple interface to some undocumented features of Oracle. As this exposes
undocumented features within Oracle there is no guarantee that the results
will be exactly as described for all releases.

    NOTE
      This package should only be installed when requested by Oracle Support.
      It is not documented in the server documentation.
      As such the package functionality may change without notice.
      It is to be used only as directed by Oracle Support and its use otherwise is not supported.

The main feature of this package is to allow user sessions to be traced
with more information than is usually available via SQL_TRACE.

The package is supplied in the files dbmssupp.sql and prvtsupp.plb.
The files are included in the distribution of the following releases (Solaris):

    8.0.6, 8.1.7, 9.0.1, 9.2.0 and above

Versions of these files may be obtained from Oracle Support for use against
Oracle releases 7.2 onwards. The package CANNOT be used in Oracle 7.1 or earlier

Procedures and Functions in DBMS_SUPPORT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The functions / procedures in this package are:

MySid Function
Start_Trace Procedure
Stop_Trace Procedure
Start_Trace_In_Session Procedure
Stop_Trace_In_Session Procedure

Each of these are described below.

FUNCTION MySid
~~~~~~~~~~~~~~
Arguments: None
Returns: Current SID as a NUMBER.

This simply returns the SID of the current session.
The function has RNPS, WNPS and WNDS RESTRICT_REFERENCES clause and so
can be used in SELECT operations.

Eg: SELECT DBMS_SUPPORT.MYSID from DUAL;

PROCEDURE Start_Trace
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Arguments: waits IN BOOLEAN DEFAULT TRUE
binds IN BOOLEAN DEFAULT FALSE

This procedure starts tracing in the current session.
The trace output is written, like SQL_TRACE, to the user trace file.
The default is to trace everything SQL_TRACE would capture plus WAIT
information.

DBMS_SUPPORT.START_TRACE;

is the default and gives information on WAITS as well as SQL_TRACE

DBMS_SUPPORT.START_TRACE( waits=>false, binds=>false )

is equivalent to standard SQL_TRACE

DBMS_SUPPORT.START_TRACE( waits=>true, binds=>false )

is the same as the default and gives information on WAITS as
well as SQL_TRACE

DBMS_SUPPORT.START_TRACE( waits=>true, binds=>true )

in addition to the above gives information about bind values.
This is typically only of use for Oracle support.

DBMS_SUPPORT.START_TRACE( waits=>false, binds=>true )

Like SQL_TRACE with additional BIND information only. No WAITs are
traced.

Known issues:

- Under MTS , XA or Oracle8 session switching the trace output may be
    spread over several different trace files and thus be of little use.

PROCEDURE Stop_Trace
~~~~~~~~~~~~~~~~~~~~
Arguments: None

This procedure stops trace in the current session.

Known issues:

- If SQL_TRACE was enabled separately this procedure will turn it off.

PROCEDURE Start_Trace_In_Session
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Arguments: sid IN NUMBER
serial IN NUMBER
waits IN BOOLEAN DEFAULT TRUE
binds IN BOOLEAN DEFAULT FALSE

This is like START_TRACE except that it allows tracing to be started in a
different session. For ease of use SERIAL may be set to NULL (or 0) to denote
the current session with the given SID.

DBMS_SUPPORT.START_TRACE_IN_SESSION( sid , null )

Will trace SQL_TRACE and WAITS in the session 'sid'.
Output is written to the target sessions trace file/s.

DBMS_SUPPORT.START_TRACE_IN_SESSION( sid , serial )

Will trace SQL_TRACE and WAITS in the session 'sid,serial#'.
You only need to specify a SERIAL if you need to be sure a
specific session is traced. If the session ends and the SID is
reused before the START_TRACE_IN_SESSION call is made including
the SERIAL will cause an error to be raised rather than the trace
being started against a different session to the one you intended
to trace.

DBMS_SUPPORT.START_TRACE_IN_SESSION( sid , 0, waits=>TRUE, binds=>TRUE )

Allows WAITS and BINDS to be traced in the target session.

Known issues:

- Under MTS , XA or Oracle8 session switching the trace output may be
    spread over several different trace files and thus be of little use.

PROCEDURE Stop_Trace_In_Session
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Arguments: sid IN NUMBER
serial IN NUMBER

This is like STOP_TRACE except that it allows tracing to be stopped in a
different session. For ease of use the SERIAL may be specified as NULL
to denote the current session with the supplied SID.

DBMS_SUPPORT.STOP_TRACE_IN_SESSION( sid , null )

will stop tracing in session SID.

Known issues:

- If SQL_TRACE was enabled separately this procedure will turn it off.

Trace Output
~~~~~~~~~~~~
The trace output is similar to SQL_TRACE output but may include additional
WAIT or BIND lines depending on the tracing options chosen.

A brief description of the trace output can be found in Note:39817.1

References
~~~~~~~~~~
Summary description of the raw trace output Note:39817.1
Description of how to use this package Note.62160.1

The DBMS_SUPPORT Package (Doc ID 62294.1)

References

Related

Products

相關文章