I’ve seen a question on the OTN forum about how to create a job in Grid Control for generating AWR/ADDM reports and sending these reports via email. As I know OEM Grid Control doesn’t have such a job template but we can write a PL/SQL script for this task and define it as a job, so we can automate it for all databases.
First, let’s check how we can generate AWR reports. To be able to get AWR reports in plain text, we can use:
Syntax (for Oracle 10.2):
DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(
l_dbid IN NUMBER,
l_inst_num IN NUMBER,
l_bid IN NUMBER,
l_eid IN NUMBER,
l_options IN NUMBER DEFAULT 0)
RETURN awrrpt_text_type_table PIPELINED;
If we want the report in HTML, we can use:
DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
l_dbid IN NUMBER,
l_inst_num IN NUMBER,
l_bid IN NUMBER,
l_eid IN NUMBER,
l_options IN NUMBER DEFAULT 0)
RETURN awrrpt_text_type_table PIPELINED;