2023-04-09

23c free - DBMS_USERDIAG

In Oracles 23c free Database, among other new packages which still need to be discovered, there is one which warms my heart. It's called DBMS_USERDIAG
Unfortunately it is not documented yet - this makes it worth to have a look at it. 

As the Package definition is human readable and proper comments are provided by developers, this can be taken as a first summary:

create or replace NONEDITIONABLE PACKAGE dbms_userdiag AUTHID CURRENT_USER AS

  PRAGMA SUPPLEMENTAL_LOG_DATA(default, READ_ONLY);

  -- last call did not return an error
  NOERROR                   CONSTANT INTEGER := 0;

  -- **********************************************************************
  -- enable_sql_trace_event
  --   Enable sql_trace event at a given level
  --   Optionally a sql_id can also be specified in which case tracing will be
  --   enabled for that sql_id alone. Default is to enable events without sql
  --   scope.
  --   Default is to set event in current session alone. Event can be disabled
  --   by setting disable to non-zero value (default zero).
  --
  -- **********************************************************************
  PROCEDURE 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
  );

  -- **********************************************************************
  -- check_sql_trace_event
  --   Checks current sql_trace event and retrieves the level
  --   sql_id is currently ignored.
  --
  -- **********************************************************************
  PROCEDURE check_sql_trace_event
  (
    level     OUT BINARY_INTEGER,
    sql_id    IN VARCHAR2 DEFAULT NULL,
    sys       IN BINARY_INTEGER DEFAULT 0
  );

  -- **********************************************************************
  -- trace
  --   Write a message to "user trace file". (default)
  --   If alert is set to non-zero value, then message is written to alert log.
  --
  -- **********************************************************************
  PROCEDURE trace
  (
    message IN VARCHAR2,
    alert   IN BINARY_INTEGER DEFAULT 0
  );

  -- **********************************************************************
  -- set_tracefile_identifier
  --    This routine is used to set a custom trace file identifier
  --    for the active tracefile.
  -- **********************************************************************

  PROCEDURE set_tracefile_identifier
  (
    trc_identifier          IN VARCHAR2
  );

  -- **********************************************************************
  -- set_exception_mode
  --    This routine sets the exception mode for the package.
  --    If exception mode is set to true, then all the exceptions
  --    will be raised to client. If it is set to false, then all the
  --    exceptions will be suppressed, and the client will not see any
  --    exception, even if underlying APIs are raising exceptions.
  --
  --    Since this is a diagnosability API, clients might not expect these
  --    API calls to raise their own exceptions.
  --    By default, exception mode will be set to false.
  --    Clients can change it any time by calling this function.
  -- **********************************************************************

  PROCEDURE set_exception_mode
  (
    exc_mode                IN BOOLEAN DEFAULT FALSE       /* exception mode */
  );

  -- **********************************************************************
  -- get_call_status
  --    This routine is used to obtain the status of the last call to the
  --    DBMS_USERDIAG API. In case the previous call was successful, the value
  --    of this call will be NOERROR(0).
  -- **********************************************************************

  FUNCTION get_call_status RETURN NUMBER;

  -- **********************************************************************
  -- get_call_error_msg
  --    This routine is used to obtain the error message if the last call
  --    to DBMS_USERDIAG API returned an error. In case the previous call was
  --    successful, the value returned by this API will be NULL.
  -- **********************************************************************

  FUNCTION get_call_error_msg RETURN VARCHAR2;

END dbms_userdiag;
By default, this package is only granted to the DBA role. 
But when granted to an user, this user can use all procedures without additional grants (like ALTER SESSION or ALTER SYSTEM). 

I managed to set_tracefile_identifier, enable trace for a level and also for a specific SQL_ID - both for the current session and also system-wide (PDB) . It seems this package can be a replacement for some functionality of DBMS_SESSION.session_trace_enable and DBMS_SYSTEM.ksdwrt.  



For completion, the list of internal functions called by these procedures - as far as I could observe them: 

ENABLE_SQL_TRACE_EVENT  dbkpls_enable_sql_trace_event
CHECK_SQL_TRACE_EVENT dbkpls_check_sql_trace_event
TRACE dbkpls_trace
SET_TRACEFILE_IDENTIFIER  dbkpls_set_tracefile_id

Keine Kommentare: