Oracle | DBMS_USERDIAG

Oracle DBMS_USERDIAG Version 23c

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 anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose User diagnostic interface for the Automatic Diagnostic Repository (ADR) for PL/SQL applications.

AUTHID DEFINER

Constants

Name	Data Type	Value
General
NOERROR	INTEGER	0

Dependencies

ADR_HOME_T

DBMS_ADRI_LIB

Documented No

First Available 23.1

Pragmas PRAGMA SUPPLEMENTAL_LOG_DATA(default, READ_ONLY);

Security Model Owned by SYS with EXECUTE granted to the DBA role.

Source {ORACLE_HOME}/rdbms/admin/dbmsadru.sql
{ORACLE_HOME}/rdbms/admin/prvtadru,plb

Subprograms

CHECK_SQL_TRACE_EVENT	GET_CALL_STATUS	SET_TRACEFILE_IDENTIFIER
ENABLE_SQL_TRACE_EVENT	SET_EXCEPTION_MODE	TRACE
GET_CALL_ERROR_MSG

CHECK_SQL_TRACE_EVENT (new 23c)

Checks the current SQL tracing event and retrieves the level (SQL_ID is ignored in 23.1) dbms_userdiag.check_sql_trace_event( level OUT BINARY_INTEGER, sql_id IN VARCHAR2 DEFAULT NULL, sys IN BINARY_INTEGER DEFAULT 0);

DECLARE

       outVal BINARY_INTEGER;

      BEGIN

        dbms_userdiag.check_sql_trace_event(outVal);

        dbms_output.put_line(outVal);

      END;

      /

      1

      

      PL/SQL procedure successfully completed.

ENABLE_SQL_TRACE_EVENT (new 23c)

Enables and disable granular SQL tracing dbms_userdiag.enable_sql_trace_event( level IN BINARY_INTEGER DEFAULT 1, sid IN BINARY_INTEGER DEFAULT 0, ser IN BINARY_INTEGER DEFAULT 0, binds IN BINARY_INTEGER DEFAULT 0, waits IN BINARY_INTEGER DEFAULT 0, plan_stat IN VARCHAR2 DEFAULT NULL, sql_id IN VARCHAR2 DEFAULT NULL, disable IN BINARY_INTEGER DEFAULT 0, sys IN BINARY_INTEGER DEFAULT 0);

exec dbms_userdiag.enable_sql_trace_event;

      

      PL/SQL procedure successfully completed.

GET_CALL_ERROR_MSG (new 23c)

Returns the error message if the last call to DBMS_USERDIAG returned an error dbms_userdiag.get_call_error_msg RETURN VARCHAR2;

SELECT dbms_userdiag.get_call_error_msg;

      

      GET_CALL_ERROR_MSG

      -------------------

GET_CALL_STATUS (new 23c)

Returns the status of the last call to the DBMS_USERDIAG API dbms_userdiag.get_call_status RETURN NUMBER;

SELECT dbms_userdiag.get_call_status;

      

       GET_CALL_STATUS

      ----------------

                     0

SET_EXCEPTION_MODE (new 23c)

Sets the package's exception mode dbms_userdiag.set_exception_mode(exc_mode IN BOOLEAN DEFAULT FALSE);

exec dbms_userdiag.set_exception_mode(TRUE);

      

      PL/SQL procedure successfully completed.

SET_TRACEFILE_IDENTIFIER (new 23c)

Set a customer trace file name dbms_userdiag.set_tracefile_identifier(trc_identifier IN VARCHAR2);

exec dbms_userdiag.set_tracefile_identifier('UW23c');

PL/SQL procedure successfully completed.

TRACE (new 23c)

Write a message to a the user trace file dbms_userdiag.trace( message IN VARCHAR2, alert IN BINARY_INTEGER DEFAULT 0);

exec dbms_userdiag.trace('This is a 23c Beta test message');PL/SQL procedure successfully completed.

SQL> host

      

      [oracle@Oracle23c-vagrant ~]$#> cd $ORACLE_BASE/diag/rdbms/orabase/ORABASE/trace

      

      [oracle@oracle23c-vagrant trace]$ ls -l *23c*

      -rw-rw----. 1 oracle oinstall 18079 Jan  6 19:13 ORABASE_ora_42961_UW23c.trc

      -rw-rw----. 1 oracle oinstall 18079 Jan  6 19:13 ORABASE_ora_42961_UW23c.trm