Oracle | DBMS_USERDIAG

Oracle DBMS_USERDIAG Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

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 23ai

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 23ai)

Checks the current SQL tracing event and retrieves the level 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 23ai)

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 23ai)

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 23ai)

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 23ai)

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 23ai)

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

exec dbms_userdiag.set_tracefile_identifier('UW23ai');

PL/SQL procedure successfully completed.

TRACE (new 23ai)

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 23ai test message');PL/SQL procedure successfully completed.

SQL> host

      

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

      

      [oracle@oracle23ai-vagrant trace]$ ls -l *23ai*

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

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