AWR SENT VIA EMAIL

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

PLSQL to sen AWR report to you 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