Plsql Package to receive an Oracle Database AWR Report sent to EMAIL

In this article, we delve into the practical implementation of using a PL/SQL package to automate the generation and distribution of Oracle Database Automatic Workload Repository (AWR) reports via email. AWR reports are essential tools for database administrators, providing in-depth performance metrics and insights that help in diagnosing issues, optimizing queries, and improving overall database performance. Manually generating these reports and sharing them with stakeholders can be time-consuming and prone to delays.

To streamline this process, we demonstrate how to create a PL/SQL package that automates the generation of AWR reports and sends them directly to your inbox or other designated recipients. We will cover the package setup, key PL/SQL procedures, and how to configure the email functionality using Oracle’s UTL_MAIL or UTL_SMTP package. This solution not only saves time but also ensures that critical performance data is consistently monitored and shared with the right team members.

By the end of this article, you will have a ready-to-use script that simplifies AWR report distribution, enhancing your ability to maintain optimal database performance with minimal manual intervention. Whether you are a seasoned DBA or new to Oracle performance tuning, this guide will help you automate a critical aspect of your database management workflow.

This is an awesome script that I found online blog post by Gokhan Atil (ORACLE ACE).I wanted to share this with my friends, as it will be very helpful in daily maintenance.

PLSQL to send the AWR report to your email directly.

DECLARE
   dbid           NUMBER;
   inst_id        NUMBER;
   bid            NUMBER;
   eid            NUMBER;
   db_unique_name VARCHAR2(30);
   host_name       VARCHAR2(64);
   starttime      CHAR (5);
   endtime        CHAR (5);
   v_from         VARCHAR2 (80);
   v_recipient    VARCHAR2 (80) := 'arvind@domain.com';
   v_mail_host    VARCHAR2 (30) := 'YOUR_SMTP_SERVER';
   v_mail_conn    UTL_SMTP.connection;
BEGIN
   starttime := '06:00';
   endtime := '10:00';

   SELECT MIN (snap_id), MAX (snap_id)
     INTO bid, eid
     FROM dba_hist_snapshot
    WHERE TO_CHAR (begin_interval_time, 'hh24:mi') >= starttime
      AND TO_CHAR (end_interval_time, 'hh24:mi') <= endtime
      AND TRUNC (begin_interval_time) = TRUNC (SYSDATE)
      AND TRUNC (end_interval_time) = TRUNC (SYSDATE);

   SELECT dbid, inst_id, db_unique_name
     INTO dbid, inst_id, db_unique_name
     FROM gv$database;

   SELECT host_name INTO host_name
     FROM v$instance;

   v_from := db_unique_name ||  '@' || host_name;

   v_mail_conn := UTL_SMTP.OPEN_CONNECTION (v_mail_host, 25);
   UTL_SMTP.HELO (v_mail_conn, v_mail_host);
   UTL_SMTP.MAIL (v_mail_conn, v_from);
   UTL_SMTP.RCPT (v_mail_conn, v_recipient);
   UTL_SMTP.OPEN_DATA( v_mail_conn );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'From:' || v_from || UTL_TCP.CRLF );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'To:' || v_recipient || UTL_TCP.CRLF );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'Subject: '
        || 'AWR Report of ' || v_from || ' '
        || SYSDATE || ' ' || starttime || '-' || endtime || UTL_TCP.CRLF  );
   UTL_SMTP.WRITE_DATA ( v_mail_conn,
        'Content-Type: text/html; charset=utf8'
        || UTL_TCP.CRLF || UTL_TCP.CRLF );  

   FOR c1_rec IN
      (SELECT output
         FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(dbid,
           inst_id, bid, eid, 8 )))
   LOOP
      UTL_SMTP.WRITE_DATA (v_mail_conn, c1_rec.output || UTL_TCP.CRLF );
   END LOOP;
 
   UTL_SMTP.CLOSE_DATA (v_mail_conn);
   UTL_SMTP.QUIT (v_mail_conn);
 
EXCEPTION
   WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR
   THEN
      RAISE_APPLICATION_ERROR (-20000, 'Unable to send mail: ' || SQLERRM);
END;



Note : 

works only if you have set the SMTP server and ACL if you are working on Database 11G or higher



Extracted from :

http://www.gokhanatil.com/2011/07/create-awr-and-addm-reports-and-send-them-via-email.html