Oracle 發送 e-mail 的功能

有些時候能夠定期由 Oracle 來寄送一些資料庫的狀況和資訊或是建立 Alert 機制，
對於 DBA 來說工作就可以輕鬆不少，也可以避免一些預期上的問題。
或是有一些報表或是執行的結果也可以透過這個方式，將  SQL 結果寄送到相關人員的 e-mail 信箱。

Oracle 提供 e-mail 傳送的功能 UTL_SMTP 來發送郵件，
只要放到 Oracle 排程裡就會定期發送 e-mail 給相關的人員，
使用此功能必須要以 sysdba 的角色來登入才能使用 (用 sys 的帳號) 。

指令看了就知道作用了，就不詳細介紹。

範例：發送 Oracle Tablespace 可用容量的 e-mail 。

declare
 --宣告
  l_mail_conn   UTL_SMTP.connection;
  l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
BEGIN
 --定義郵件主機，不能用 IP 只能用 host name，可以在 /etc/hosts 增加
  l_mail_conn := UTL_SMTP.open_connection('mailserver', '25');
  UTL_SMTP.helo(l_mail_conn, 'mailserver');

  --寄件者
  UTL_SMTP.mail(l_mail_conn, '4shiun@gmail.com');

  --多個收件者
  UTL_SMTP.rcpt(l_mail_conn, '4shiun@gmail.com');

  UTL_SMTP.open_data(l_mail_conn);

  --以 HTML 方式來傳送，定義收件者和寄件者名稱
  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || 'Adam' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || 'Adam' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || 'Tablespace Information' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || 'Adam' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

  UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

  --把 Tablespace 的資料用 Table 來顯示
  UTL_SMTP.write_data(l_mail_conn, '<table border=1 width=800px>');
  UTL_SMTP.write_data(l_mail_conn, '<TR align=center><TD>tablespace_name</TD><TD>free</TD><TD>used</TD><TD>total</TD><TD>used_percent</TD><TD>free_percent</TD></TR>');
  for i in(select a.tablespace_name,to_char(b.free,'fm999,999,999,999') free,to_char(a.total-b.free,'fm999,999,999,999') used,to_char(a.total,'fm999,999,999,999') total,to_char(((a.total - b.free)/a.total)*100,'999.99')||'%' used_percent,to_char((b.free/a.total)*100,'999.99')||'%' free_percent from (select tablespace_name,sum(bytes) total from dba_data_files group  by tablespace_name)a,(select tablespace_name,sum(bytes) free from dba_free_space group by tablespace_name) b where a.tablespace_name= b.tablespace_name order by 1)
      loop
      UTL_SMTP.write_data(l_mail_conn, '<TR align=right><TD align=left>'||i.tablespace_name||'</TD><TD>'||i.free||'</TD><TD>'||i.used||'</TD><TD>'||i.total||'</TD><TD>'||i.used_percent||'</TD><TD>'||i.free_percent||'</TD></TR>');
      end loop;
  UTL_SMTP.write_data(l_mail_conn, '</table>');
  UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

  UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);
END;

發送 e-mail 當然也可以把 Blob 欄位的資料當成附件的方式來傳送。

要注意的是，如果 mail server 有認證才能發送 e-mail 的話，就需要再加上帳號和密碼，

發送的 e-mail 如果是中文的話，HTML 的文字編碼也需要修改。

建議是把此 SQL 指令寫成 PROCEDURE 的方式方便執行。