Oracle | DBMS_AUTO

Oracle DBMS_AUTO_REPORT 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 Provides an interface to view SQL Monitoring and Real-time Automatic Database Diagnostic Monitor (ADDM) data that has been captured into Automatic Workload Repository (AWR). It also provides subprograms to control the behavior of how these data are captured to AWR.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
ERR_FIN_CAPTURE	NUMBER	-13554

Dependencies

DBA_DIRECTORIES	DBMS_SQLTUNE	PRVT_REPORT_TAGS
DBMS_DATAPUMP	DBMS_SQLTUNE_UTIL2	V$TIMER
DBMS_LOCK	DBMS_STANDARD	V_$SYS_REPORT_STATS
DBMS_OUTPUT	DBMS_SYS_ERROR	WRI$_REPT_ARC
DBMS_PERF	DUAL	WRP$_REPORTS_CONTROL
DBMS_REPORT	GV_$INSTANCE	XMLTYPE
DBMS_REPORT_LIB	PRVTEMX_PERF	XQSEQUENCE

Documented Yes: Packages and Types ReferencePartially

Exceptions

Error Code	Reason
ORA-13991	ERR_FIN_CAPTURE

First Available 12.1

Security Model Owned by SYS with EXECUTE granted to PUBLIC

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

Subprograms

EXPORT_REPORTS	PURGE_CALL_REPORTS	REPORT_REPOSITORY_LIST_XML
FINISH_REPORT_CAPTURE	PURGE_REPORTS_BY_RETENTION	START_REPORT_CAPTURE
FINISH_REPORT_CAPTURE_HELPER	REPORT_REPOSITORY_DETAIL	START_REPORT_CAPTURE_HELPER
IMPORT_REPORTS	REPORT_REPOSITORY_DETAIL_XML

EXPORT_REPORTS (new 23ai)

Exports reports matching the dbid between the beginning and ending snapshot ids

We recommend considering this functionality a security issue as execute is granted to PUBLIC dbms_auto_report.export_reports( dbid IN NUMBER, bid IN NUMBER DEFAULT 1, eid IN NUMBER DEFAULT 1000000, dmpdir IN VARCHAR2 DEFAULT 'DATA_PUMP_DIR', dmpfile IN VARCHAR2 DEFAULT 'sqlmondat');

exec dbms_auto_report.export_reports;

      

      PL/SQL procedure successfully completed.

FINISH_REPORT_CAPTURE

Ends the complete capture of SQL monitor data that was started with START_REPORT_CAPTURE dbms_auto_report.finish_report_capture;

exec dbms_auto_report.finish_report_capture;

      

      PL/SQL procedure successfully completed.

FINISH_REPORT_CAPTURE_HELPER

Internal Undocumented dbms_auto_report.finish_report_capture_helper RETURN NUMBER;

See START_REPORT_CAPTURE_HELPER Demo Below

IMPORT_REPORTS (new 23ai)

Imports reports from the specified dumpfile pointed to by the database directory dbms_auto_report.import_reports( dmpdir IN VARCHAR2 DEFAULT 'DATA_PUMP_DIR', dmpfile IN VARCHAR2);

exec dbms_auto_report.import_reports(dmpfile=>'sqlmondat');

      

      PL/SQL procedure successfully completed.

PURGE_ALL_REPORTS (new 23ai)

Purges all SQLMON content from the wrp$tables associated with the dbid dbms_auto_report.purge_all_reports(dbid IN NUMBER DEFAULT NULL);

exec dbms_auto_report.pure_all_reports(1863203691);

      

      PL/SQL procedure successfully completed.

PURGE_REPORTS_BY_RETENTION (new 23ai)

Purges all SQLMON content from the wrp$tables associated with the dbid only if the reports are older than the specified retention period

No information has been found as to how to set and retrieve the retention period so be very careful dbms_auto_report.purge_reports_by_retention(dbid IN NUMBER DEFAULT NULL);

exec dbms_auto_report.purge_reports_by_retention(1863203691);

      

      PL/SQL procedure successfully completed.

REPORT_REPOSITORY_DETAIL

Obtains the stored XML report for a given report ID dbms_auto_report.report_repository_detail( rid IN NUMBER DEFAULT NULL, type IN VARCHAR2 DEFAULT 'XML', base_path IN VARCHAR2 DEFAULT NULL) RETURN CLOB;

SELECT dbms_auto_report.report_repository_detail(569);

      

      -- output is unformatted

REPORT_REPOSITORY_DETAIL_XML

Obtains the stored XML report for a given report ID dbms_auto_report.report_repository_detail_xml( rid IN NUMBER DEFAULT NULL, base_path IN VARCHAR2 DEFAULT NULL) RETURN XMLTYPE;

SELECT dbms_auto_report.report_repository_detail_xml(569);

      

      -- output is formatted

REPORT_REPOSITORY_LIST_XML

Obtains an XML report of the list of SQL Monitor and Real-time ADDM data captured in AWR dbms_auto_report.report_repository_list_xml( active_since IN DATE DEFAULT NULL, active_upto IN DATE DEFAULT NULL, snapshot_id IN NUMBER DEFAULT NULL, dbid IN NUMBER DEFAULT NULL, inst_id IN NUMBER DEFAULT NULL, con_dbid IN NUMBER DEFAULT NULL, session_id IN NUMBER DEFAULT NULL, session_serial IN NUMBER DEFAULT NULL, component_name IN VARCHAR2 DEFAULT NULL, key1 IN VARCHAR2 DEFAULT NULL, key2 IN VARCHAR2 DEFAULT NULL, key3 IN VARCHAR2 DEFAULT NULL, report_level IN VARCHAR2 DEFAULT 'TYPICAL', base_path IN VARCHAR2 DEFAULT NULL, top_n_count IN NUMBER DEFAULT NULL, top_n_rankby IN VARCHAR2 DEFAULT 'db_time', top_n_detail_count IN NUMBER) RETURN XMLTYPE;

See REPORT_REPOSITORY_DETAIL Demo Above

START_REPORT_CAPTURE

Captures SQL monitor data of any newly monitored SQLs every minute since the last run of the capture cycle, and stores it in AWR. dbms_auto_report.start_report_capture;

exec dbms_auto_report.start_report_capture;

START_REPORT_CAPTURE_HELPER

Internal Undocumented dbms_auto_report.start_report_capture_helper RETURN NUMBER;

SELECT dbms_auto_report.start_report_capture_helper;

      

      START_REPORT_CAPTURE_HELPER

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

                                1

      

      SELECT 
      dbms_auto_report.finish_report_capture_helper;

      

      FINISH_REPORT_CAPTURE_HELPER

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

                                 1