Scheduling AWR from OEM | a PL sql Script

Schedule the below report in OEM. it will generate a report for 12 hour and send to your email box.

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) := '----------------------Email _D----------------';

   v_mail_host    VARCHAR2 (30) := '-----------------------SMTP SERVER------------------'';

   v_mail_conn    UTL_SMTP.connection;

BEGIN

   starttime := '00:00';

   endtime := '23:59';

SELECT Max(snap_id)-1, 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,db_unique_name INTO dbid,db_unique_name FROM v$database;

                select INSTANCE_NUMBER into inst_id from v$instance;

   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;

/