?

一.? dba_errors 表

?

官網對這個表的說明如下：

?

ALL_ERRORS：describes the current errors on the stored objects accessible to the current user.

DBA_ERRORS：describes the current errors on all stored objects in the database.

?

USER_ERRORS： describes the current errors on the stored objects owned by the current user. This view does not display the OWNER column.

?

示例：

SQL> select owner,name,type,text,attribute from dba_errors;

?

OWNER????? NAME????????????????????? TYPE???????? TEXT?????????????????????????????????????????????? ATTRIBUTE

---------- ------------------------- ------------ -------------------------------------------------- ---------

SYS??????? PROC_ALARM_KC_SCB???????? PROCEDURE??? PL/SQL: ORA-00942: table or view does not exist??? ERROR

SYS??????? PROC_ALARM_KC_SCB???????? PROCEDURE??? PL/SQL: SQL Statement ignored????????????????????? ERROR

SYS??????? PROC_ALARM_KC_SCB???????? PROCEDURE??? PLS-00201: identifier 'JOBLOG.WRITELOG' must be de ERROR

SYS??????? PROC_ALARM_KC_SCB???????? PROCEDURE??? PL/SQL: Statement ignored????????????????????????? ERROR

SYS??????? PROC_ALARM_KC_SCB???????? PROCEDURE??? PLS-00201: identifier 'RUNLOG.ERRORLOG' must be de ERROR

SYS??????? PROC_ALARM_KC_SCB???????? PROCEDURE??? PL/SQL: Statement ignored????????????????????????? ERROR

?

?

?????? 從這個表里面，我們可以看到數據庫當前存在的錯誤，這些錯誤信息包含對象名稱，類型，所有者，錯誤類型和錯誤原因。

?????? 如果該表里有這些錯誤，就可以調用Oracle 的發送郵件的存儲過程，將這些信息發送到相關郵箱或者移動的139郵箱，該郵箱可以直接將郵件發送到手機。 這樣就可以起到一個及時通知的作用。

?

發送郵件的存儲過程參考Blog：

??????

Oracle 發送郵件 存儲過程

http://blog.csdn.net/tianlesoftware/archive/2010/08/27/5842754.aspx

?

二. 存儲過程

?

/* Formatted on 2011/5/19 20:48:30 (QP5 v5.163.1008.3004) */

CREATE OR REPLACE PROCEDURE getsenterrlog

AS

?? email_title????? VARCHAR2 (100);????????????????????????????? ???--email 標題

?? email_content??? VARCHAR2 (1000);??????????????????????????????? --email 內容

?? email_server???? VARCHAR2 (20);??????????????????????????????? -- email 服務器

?? email_sender???? VARCHAR2 (20);??????????????????????????????? --email 發送郵箱

?? email_user?????? VARCHAR2 (20);????????????????????????????????? --email 用戶

?? email_pwd??????? VARCHAR2 (20);?????????????????????????????????? --emai 密碼

?? email_receiver1?? VARCHAR2 (100);?????????????????????????????? --email? 接收人

?? email_receiver2?? VARCHAR2 (100);?????????????????????????????? --email? 接收人

?? email_receiver3?? VARCHAR2 (100);?????????????????????????????? --email? 接收人

?? CURSOR c1

?? IS

????? SELECT * FROM dba_errors;

BEGIN

?? /**

?? 過程內容： 查詢dba_errors 表，如果有錯誤，就發送到139郵箱

?? 作者： Dave

?? 時間：2011-5-19

?? */

?? email_server := '192.168.1.100';

?? email_sender := 'dvd.dba@gmail.com';

?? email_user := 'tianlesoftware';

?? email_pwd := 'pwd';

?? email_receiver1 :=

????? '13888888888@139.com;dvd.dba@gmail.com';

?? email_receiver2 :=

????? '13888888888@139.com;dvd.dba@gmail.com';

?? email_receiver3 :=

????? '13888888888@139.com;dvd.dba@gmail.com';

?

?? FOR x IN c1

?? LOOP

????? IF x.owner = 'SYS'

????? THEN

???????? email_title := x.owner|| ',s '|| x.TYPE|| ': '|| x.name|| ' report ' || x.attribute || '!';

???????? email_content := x.text;

???????? --? DBMS_OUTPUT.put_line (email_title);

???????? --DBMS_OUTPUT.put_line (email_content);

???????? procsendemail (email_content,email_title, email_sender,email_receiver1, email_server,25,1,email_user,email_pwd, '', 'bit 7');

????? ELSIF x.owner = 'SYSTEM'

????? THEN

???????? email_title := x.owner|| ',s '|| x.TYPE|| ': '|| x.name|| ' report ' || x.attribute || '!';

???????? email_content := x.text;

???????? --? DBMS_OUTPUT.put_line (email_title);

???????? --DBMS_OUTPUT.put_line (email_content);

????????????? --調用發送郵件過程

???????? procsendemail (email_content,email_title, email_sender,email_receiver2, email_server,25,1,email_user,email_pwd, '', 'bit 7');

????? ELSE

???????? email_title := x.owner|| ',s '|| x.TYPE|| ': '|| x.name|| ' report ' || x.attribute || '!';

???????? email_content := x.text;

???????? --? DBMS_OUTPUT.put_line (email_title);

???????? --DBMS_OUTPUT.put_line (email_content);

???????? procsendemail (email_content,email_title, email_sender,email_receiver3, email_server,25,1,email_user,email_pwd, '', 'bit 7');

????? END IF;

?? END LOOP;

EXCEPTION

?? WHEN NO_DATA_FOUND

?? THEN

????? RETURN;

?? WHEN OTHERS

?? THEN

????? RETURN;

END;

?

?????? 不同用戶的錯誤，發送到相關的責任人，當然DBA 肯定都要收到。

?

?

三. 使用Scheduler Job 部署

?

Oracle 10g Scheduler 特性

http://blog.csdn.net/tianlesoftware/archive/2009/10/22/4715218.aspx

?

3.1 創建Job：

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => 'Jobgetsenterrlog',

job_type => 'STORED_PROCEDURE',

job_action => 'GETSENTERRLOG',? --調用的過程名稱

start_date => sysdate,

repeat_interval => 'FREQ=MINUTELY;INTERVAL=1'); -- 每個一分鐘執行一次

END;

/

?

?????? 關于這些參數的說明，參考上面的鏈接。

?

注意：

?????? JOB 雖然成功創建了，但卻并未執行.因為ENABLED 參數當不顯式指定時，該參數的默認值為false。

?

?

3.2 啟用Job

exec dbms_scheduler.enable('Jobgetsenterrlog');

?

?

3.3 停止Job

exec dbms_scheduler.disable(' Jobgetsenterrlog');

?

?

?

?

?

?

?

?

?

-------------------------------------------------------------------------------------------------------

Blog： http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群：62697716(滿);?? DBA2 群：62697977(滿)?? DBA3 群：62697850(滿)??

DBA 超級群：63306533(滿);? DBA4 群： 83829929? DBA5群： 142216823???

DBA6 群：158654907? 聊天 群：40132017?? 聊天2群：69087192

--加群需要在備注說明Oracle表空間和數據文件的關系，否則拒絕申請

轉載于:https://www.cnblogs.com/springside-example/archive/2011/05/19/2529730.html

總結

以上是生活随笔為你收集整理的根据 dba_errors 制定 数据库报警邮件的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。