Oracle DBMS_AUTO

Oracle DBMS_AUTO_REPORT Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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_HIST_REPORTS	DBMS_SQLTUNE_UTIL2	V$TIMER
DBA_HIST_REPORTS_DETAILS	DBMS_SYS_ERROR	V_$SYS_REPORT_STATS
DBMS_LOCK	DUAL	WRI$_REPT_ARC
DBMS_PERF	GV_$INSTANCE	WRP$_REPORTS_CONTROL
DBMS_REPORT	PRVTEMX_PERF	XMLTYPE
DBMS_REPORT_LIB	PRVT_REPORT_TAGS	XQSEQUENCE
DBMS_SQLTUNE

Documented Yes

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

FINISH_REPORT_CAPTURE	REPORT_REPOSITORY_DETAIL_XML	START_REPORT_CAPTURE
FINISH_REPORT_CAPTURE_HELPER	REPORT_REPOSITORY_LIST_XML	START_REPORT_CAPTURE_HELPER
REPORT_REPOSITORY_DETAIL

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;

FINISH_REPORT_CAPTURE_HELPER

Internal Undocumented dbms_auto_report.finish_report_capture_helper RETURN NUMBER;

See START_REPORT_CAPTURE_HELPER Demo Below

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_list_xml(SYSDATE-1)

      FROM dual;

      

      SELECT dbms_auto_report.report_repository_detail(569)

      FROM dual;

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_list_xml(SYSDATE-1)

      FROM dual;

      

      SELECT dbms_auto_report.report_repository_detail_xml(569)

      FROM dual;

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

      FROM dual;

      

      START_REPORT_CAPTURE_HELPER

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

                                1

      

      SELECT 
      dbms_auto_report.finish_report_capture_helper

      FROM dual;

      

      FINISH_REPORT_CAPTURE_HELPER

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

                                 1