一.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;
OWNERNAMETYPETEXTATTRIBUTE
---------- ------------------------- ------------ -------------------------------------------------- ---------
SYSPROC_ALARM_KC_SCBPROCEDUREPL/SQL: ORA-00942: table or view does not existERROR
SYSPROC_ALARM_KC_SCBPROCEDUREPL/SQL: SQL Statement ignoredERROR
SYSPROC_ALARM_KC_SCBPROCEDUREPLS-00201: identifier 'JOBLOG.WRITELOG' must be de ERROR
SYSPROC_ALARM_KC_SCBPROCEDUREPL/SQL: Statement ignoredERROR
SYSPROC_ALARM_KC_SCBPROCEDUREPLS-00201: identifier 'RUNLOG.ERRORLOG' must be de ERROR
SYSPROC_ALARM_KC_SCBPROCEDUREPL/SQL: Statement ignoredERROR
从这个表里面,我们可以看到数据库当前存在的错误,这些错误信息包含对象名称,类型,所有者,错误类型和错误原因。
如果该表里有这些错误,就可以调用Oracle的发送邮件的存储过程,将这些信息发送到相关邮箱或者移动的139邮箱,该邮箱可以直接将邮件发送到手机。这样就可以起到一个及时通知的作用。
发送邮件的存储过程参考Blog:
Oracle发送邮件存储过程
http://blog.youkuaiyun.com/xujinyang/article/details/6830560
二.存储过程
/* Formatted on 2011/5/19 20:48:30 (QP5 v5.163.1008.3004) */
CREATEORREPLACEPROCEDUREgetsenterrlog
AS
email_titleVARCHAR2(100);--email标题
email_contentVARCHAR2(1000);--email内容
email_serverVARCHAR2(20);-- email服务器
email_senderVARCHAR2(20);--email发送邮箱
email_userVARCHAR2(20);--email用户
email_pwdVARCHAR2(20);--emai密码
email_receiver1VARCHAR2(100);--email接收人
email_receiver2VARCHAR2(100);--email接收人
email_receiver3VARCHAR2(100);--email接收人
CURSORc1
IS
SELECT*FROMdba_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';
FORxINc1
LOOP
IFx.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');
ELSIFx.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');
ENDIF;
ENDLOOP;
EXCEPTION
WHENNO_DATA_FOUND
THEN
RETURN;
WHENOTHERS
THEN
RETURN;
END;
不同用户的错误,发送到相关的责任人,当然DBA肯定都要收到。
三.使用Scheduler Job部署
Oracle 10g Scheduler特性
http://blog.youkuaiyun.com/xujinyang/article/details/6830222
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');
-------------------------------------------------------------------------------------------------------