Create AWR and ADDM Reports and Send Them via Email

I’ve seen a question on OTN forum about how to create a job in Grid Control for generating AWR/ADDM reports and send 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;