The DBMS_SUPPORT Package (Doc ID 62294.1)

rongshiyuan發表於2014-04-02
Package
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




References

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-1134365/，如需轉載，請註明出處，否則將追究法律責任。

相關文章