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 will be available on generic Linux platforms in January and soon on AIX and Windows

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