Oracle DBMS_AUTO_REPORT
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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 WRI$_REPT_ARC
DBMS_LOCK DUAL WRP$_REPORTS_CONTROL
DBMS_PERF PRVTEMX_PERF XMLSEQUENCETYPE
DBMS_REPORT PRVT_REPORT_TAGS XMLTYPE
DBMS_REPORT_LIB V$INSTANCE XQSEQUENCE
DBMS_SQLTUNE V$SYS_REPORT_STATS  
Documented Yes
First Available 12.1.0
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsautorep.sql
Subprograms
 
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;

-- the report identifier (rid) was obtained using from the report XML. The line looked like this:
 <auto_report id="
569" snapshot_id="567" database_id="2611279002" component_name="sqlmonitor" period_start_time="01/12
/2013 09:56:26" period_end_time="01/12/2013 09:56:46" generation_time="01/12/2013 09:57:18" sql_id="guja1mzkt9nwq" sql_ex
ec_id="16777216" sql_exec_start="01:12:2013 09:56:26">


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;

-- the report identifier (rid) was obtained using from the report XML. The line looked like this:
 <auto_report id="
569" snapshot_id="567" database_id="2611279002" component_name="sqlmonitor" period_start_time="01/12
/2013 09:56:26" period_end_time="01/12/2013 09:56:46" generation_time="01/12/2013 09:57:18" sql_id="guja1mzkt9nwq" sql_ex
ec_id="16777216" sql_exec_start="01:12:2013 09:56:26">


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;

SELECT dbms_auto_report.finish_report_capture_helper
FROM dual;

Related Topics
DBMS_ADDM
DBMS_AUTO_REPORT_INTERNAL
DBMS_AWR_REPORT_LAYOUT
DBMS_REPORT
DBMS_WORKLOAD_REPOSITORY
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved