Oracle | DBMS_SQL

Oracle DBMS_SQL_MONITOR 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 Provides information about Real-time SQL and Database Operation Monitoring.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Date Format
DATE_FMT	VARCHAR2(21)	'mm/dd/yyyy hh24:mi:ss'
Forced Tracking
FORCE_TRACKING	VARCHAR2(30)	'Y'
NO_FORCE_TRACKING	VARCHAR2(30)	'N'
Monitor Types
MONITOR_TYPE_SQL	NUMBER	1
MONITOR_TYPE_DBOP	NUMBER	2
MONITOR_TYPE_ALL	NUMBER	3
Report Levels
LEVEL_ALL	VARCHAR2(3)	'ALL'
LEVEL_BASIC	VARCHAR2(5)	'BASIC'
LEVEL_TYPICAL	VARCHAR2(7)	'TYPICAL'
Report Sections
SECTION_ALL	VARCHAR2(3)	'ALL'
SECTION_ERRORS	VARCHAR2(6)	'ERRORS'
SECTION_FINDINGS	VARCHAR2(8)	'FINDINGS'
SECTION_INFORMATION	VARCHAR2(11)	'INFORMATION'
SECTION_PLANS	VARCHAR2(5)	'PLANS'
SECTION_SUMMARY	VARCHAR2(7)	'SUMMARY'
Report Types
TYPE_HTML	VARCHAR2(4)	'HTML'
TYPE_TEXT	VARCHAR2(4)	'TEXT'
TYPE_XML	VARCHAR2(3)	'xML'

Dependencies

CDB_SQL_MONITOR_USAGE	DBMS_SQLMON_LIB	GV$SQL_MONITOR
DBA_SQL_MONITOR_USAGE	DBMS_SQLTUNE	GV$SQL_MONITOR_STATNAME
DBMS_DEBUG_JDWP	DBMS_SQLTUNE_UTIL1	KUPM$MCP
DBMS_HPROF	DBMS_SYS_ERROR	XMLTYPE
DBMS_REPORT	DUAL

Documented Yes

Exceptions

Error Code	Reason
ORA-13812	Current session does not have database operation monitored

First Available 12.1

Security Model Owned by SYS with EXECUTE granted to PUBLIC. The reporting functionality requires the SELECT_CATALOG_ROLE role.

If anyone can think of a reason why EXECUTE should be granted to PUBLIC rather than a privileged account I would appreciate an email with that explanation.

Source {ORACLE_HOME}/rdbms/admin/dbmssqlm.sql

Subprograms

BEGIN_OPERATION	REPORT_SQL_MONITOR	REPORT_SQL_MONITOR_LIST_XML
END_OPERATION	REPORT_SQL_MONITOR_LIST	REPORT_SQL_MONITOR_XML

BEGIN_OPERATION

Starts a composite database operation in the current session dbms_sql_monitor.begin_operation( dbop_name IN VARCHAR2, dbop_eid IN NUMBER := NULL, forced_tracking IN VARCHAR2 := NO_FORCE_TRACKING, attribute_list IN VARCHAR2 := NULL, session_id IN NUMBER := NULL, session_serial IN NUMBER := NULL) RETURN NUMBER;

DECLARE

       n NUMBER;

      BEGIN

        n := dbms_sql_monitor.begin_operation('UWMonOps', attribute_list => 'table_name = sys.zzyzx, operation=load');

        dbms_output.put_line(TO_CHAR(n));

      END;

      /

      1

      

      PL/SQL procedure successfully completed.

      

      SELECT UNIQUE dbop_name

      FROM gv$sql_monitor;

      

      DBOP_NAME

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

      UWMonOps

      

      CREATE TABLE zzyzx AS

      SELECT * FROM dba_objects

      WHERE 1=2;

      

      Table created.

      

      INSERT INTO sys.zzyzx SELECT * FROM dba_objects;

      

      74838 rows created.

      

      COMMIT;

      

      Commit complete.

      

      INSERT /*+APPEND*/ INTO sys.zzyzx SELECT * FROM dba_objects;

      

      74838 rows created.

      

      COMMIT;

      

      Commit complete.

      

      DECLARE

       x XMLTYPE;

      BEGIN

        x := dbms_sql_monitor.report_sql_monitor_xml(dbop_name=>'UWMonOps');

        dbms_output.put_line(x.getCLOBVal());

      END;

      /

      

      -- xml manually formatted

      <report db_version="23.0.0.0.0" elapsed_time="1.09" cpu_time="1.08" cpu_cores="8"

      hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" 
      dbtz_offset="0" time_at_dbtz="off" packs="2"service_type="0">

        <report_id><![CDATA 

      ...

        </sql_monitor_report>

      </report>

      

      PL/SQL procedure successfully completed.

      

      BEGIN

        dbms_sql_monitor.end_operation('UWMonOps', 1);

      END;

      /

      

      PL/SQL procedure successfully completed.

      

      SELECT UNIQUE dbop_name

      FROM gv$sql_monitor;

      

      DBOP_NAME

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

END_OPERATION

Called to end the operation in the current session. If there is no operation, this will be NO-OP. dbms_sql_monitor.end_operation( dbop_name IN VARCHAR2, dbop_eid IN NUMBER);

See BEGIN OPERATION Demo Above

REPORT_SQL_MONITOR

Builds a detailed report for a specific database operation that has been monitored by Oracle dbms_sql_monitor.report_sql_monitor( sql_id IN VARCHAR2 DEFAULT NULL, dbop_name IN VARCHAR2 DEFAULT NULL, dbop_exec_id IN NUMBER DEFAULT NULL, session_id IN NUMBER DEFAULT NULL, session_serial IN NUMBER DEFAULT NULL, sql_exec_start IN DATE DEFAULT NULL, sql_exec_id IN NUMBER DEFAULT NULL, inst_id IN NUMBER DEFAULT NULL, start_time_filter IN DATE DEFAULT NULL, end_time_filter IN DATE DEFAULT NULL, instance_id_filter IN NUMBER DEFAULT NULL, parallel_filter IN VARCHAR2 DEFAULT NULL, plan_line_filter IN NUMBER DEFAULT NULL, event_detail IN VARCHAR2 DEFAULT 'YES', bucket_max_count IN NUMBER DEFAULT 128, bucket_interval IN NUMBER DEFAULT NULL, base_path IN VARCHAR2 DEFAULT NULL, last_refresh_time IN DATE DEFAULT NULL, report_level IN VARCHAR2 DEFAULT 'TYPICAL', type IN VARCHAR2 DEFAULT 'TEXT', sql_plan_hash_value IN NUMBER DEFAULT NULL, con_name IN VARCHAR2 DEFAULT NULL) RETURN CLOB;

DECLARE

       c CLOB;

      BEGIN

        c := dbms_sql_monitor.report_sql_monitor(dbop_name=>'UWMonOps');

        dbms_output.put_line(c);

      END;

      /

      

      SQL Monitoring Report

      

      Global Information

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

      Status                                : EXECUTING

      

      Instance ID                           : 1

      Session                               : SYS (162:4878)

      DBOP Name                             : UWMonOps

      DBOP Execution ID                     : 3

      First Refresh Time                    : 09/30/2024 21:47:33

      Last Refresh Time                     : 09/30/2024 21:48:20

      Duration                              : 60s

      

      Module/Action                         : sqlplus@asra23ai (TNS V1-V3)/=

      Service                               : SYS$USERS

      Program                               : sqlplus.exe

      PLSQL Entry Ids (Object/Subprogram)   : 18069,3

      PLSQL Current Ids (Object/Subprogram) : 17045,162

      

      Global Stats

      =============================================================================

      |Elapsed|CPU    |IO      |Application|Other   |Buffer|Read|Read |Write|Write|

      |Time(s)|Time(s)|Waits(s)|Waits(s)   |Waits(s)|Gets  |Reqs|Bytes|Reqs |Bytes|

      =============================================================================

      |  0.67 |  0.66|   0.00 |      
      0.00 |   0.00 | 1662 |   1 |8192 |  14 | 13MB|

      =============================================================================

      

      PL/SQL procedure successfully completed.

REPORT_SQL_MONITOR_LIST

Builds a report for all or a subset of database operations that have been monitored by Oracle dbms_sql_monitor.report_sql_monitor_list( sql_id IN VARCHAR2 DEFAULT NULL, dbop_name IN VARCHAR2 DEFAULT NULL, monitor_type IN NUMBER DEFAULT MONITOR_TYPE_ALL, session_id IN NUMBER DEFAULT NULL, session_serial IN NUMBER DEFAULT NULL, inst_id IN NUMBER DEFAULT NULL, active_since_date IN DATE DEFAULT NULL, active_since_sec IN NUMBER DEFAULT NULL, last_refresh_time IN DATE DEFAULT NULL, report_level IN VARCHAR2 DEFAULT 'TYPICAL', auto_refresh IN NUMBER DEFAULT NULL, base_path IN VARCHAR2 DEFAULT NULL, type IN VARCHAR2 DEFAULT 'TEXT', con_name IN VARCHAR2 DEFAULT NULL) RETURN CLOB;

DECLARE

       c CLOB;

      BEGIN

        c := dbms_sql_monitor.report_sql_monitor_list(dbop_name => 'UWMonOps');

        dbms_output.put_line(c);

      END;

      /

REPORT_SQL_MONITOR_LIST_XML

Builds a report for all or a subset of database operations that have been monitored by Oracle in XML dbms_sql_monitor.report_sql_monitor_list_xml( sql_id IN VARCHAR2 DEFAULT NULL, dbop_name IN VARCHAR2 DEFAULT NULL, monitor_type IN NUMBER DEFAULT MONITOR_TYPE_ALL, session_id IN NUMBER DEFAULT NULL, session_serial IN NUMBER DEFAULT NULL, inst_id IN NUMBER DEFAULT NULL, active_since_date IN DATE DEFAULT NULL, active_since_sec IN NUMBER DEFAULT NULL, last_refresh_time IN DATE DEFAULT NULL, report_level IN VARCHAR2 DEFAULT 'TYPICAL', auto_refresh IN NUMBER DEFAULT NULL, base_path IN VARCHAR2 DEFAULT NULL, con_name IN VARCHAR2 DEFAULT NULL) RETURN XMLTYPE;

DECLARE

       x XMLTYPE;

      BEGIN

        x := dbms_sql_monitor.report_sql_monitor_list_xml('UWMonOps');

        dbms_output.put_line(x.getCLOBVal());

      END;

      /

REPORT_SQL_MONITOR_XML

Builds a detailed report for a specific database operation that has been monitored by Oracle in XML dbms_sql_monitor.report_sql_monitor_xml( sql_id IN VARCHAR2 DEFAULT NULL, dbop_name IN VARCHAR2 DEFAULT NULL, dbop_exec_id IN NUMBER DEFAULT NULL, session_id IN NUMBER DEFAULT NULL, session_serial IN NUMBER DEFAULT NULL, sql_exec_start IN DATE DEFAULT NULL, sql_exec_id IN NUMBER DEFAULT NULL, inst_id IN NUMBER DEFAULT NULL, start_time_filter IN DATE DEFAULT NULL, end_time_filter IN DATE DEFAULT NULL, instance_id_filter IN NUMBER DEFAULT NULL, parallel_filter IN VARCHAR2 DEFAULT NULL, plan_line_filter IN NUMBER DEFAULT NULL, event_detail IN VARCHAR2 DEFAULT 'YES', bucket_max_count IN NUMBER DEFAULT 128, bucket_interval IN NUMBER DEFAULT NULL, base_path IN VARCHAR2 DEFAULT NULL, last_refresh_time IN DATE DEFAULT NULL, report_level IN VARCHAR2 DEFAULT 'TYPICAL', auto_refresh IN NUMBER DEFAULT NULL, sql_plan_hash_value IN NUMBER DEFAULT NULL, con_name IN VARCHAR2 DEFAULT NULL) RETURN XMLTYPE;

See BEGIN_OPERATIONS Demo Above