一. 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://www.cndba.cn/Dave/article/420
二. 存储过程
/* 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://www.cndba.cn/Dave/article/1258
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');
附:job不能自动运行。
本文转自【乐沙弥的世界】,在此感谢原作者!
Oracle jobs为Oracle开发人员和数据库管理员提供了数据库层面维护的极大便利性。对于Oracle jobs在Oracle 9i之前,是由dbms_jobs来实现,而到了10g之后,多出了dbms_scheduler方式。两者同样可以添加Oracle job,只不过dbms_scheduler的功能更为强大。在使用Oracle jobs时,我们不得不关注job_queue_processes参数,用于设定job队列可以启动的进程数。本文即是围绕此展开。查看该参数命令:
show parameter job;
1、job_queue_processes参数
下面是11g reference的描述:
a、从上面的描述可知,对于Oracle job进程,包含协调进程(主进程)以及奴隶进程(子进程)。
b、job_queue_processes取值范围为0到1000,总共可创建多少个job进程由job_queue_processes参数来决定。
c、当job_queue_processes大于1时,且并行执行job时,至少一个为协调进程。其总数不会超出job_queue_processes的值。
d、job_queue_processes参数的值为且DBMS_JOB与DBMS_SCHEDULER共享。
e、job_queue_processes参数,当设定该值为0的时候则任意方式创建的job都不会运行。
f、非零值的job_queue_processes,其job子进程数依赖于可用资源,资源配置方式以及当前运行的job数来自行调整。
g、此外对于Scheduler jobs方式还受限制于scheduler属性MAX_JOB_SLAVE_PROCESSES的设置。
h、可以通过DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE来设置max_job_slave_processes
2、测试参数job_queue_processes为1的情形
- -->演示环境
- SQL>
select * from v$version where rownum<2; -
- BANNER
- --------------------------------------------------------------------------------
- Oracle
Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production -
- -->创建测试用表
- CREATE
TABLE tb_job - (
-
job_name VARCHAR2 (5), -
update_dt VARCHAR2 (20) - );
-
- -->添加多个Oracle
job来并发执行 - SQL>
ho more add_job.sql - DECLARE
-
job_name VARCHAR2 (20); - BEGIN
-
DBMS_OUTPUT.put_line ('Current sysdate is ' || TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss')); -
-
FOR i IN 1 .. 5 -
LOOP -
job_name := 'JOB_' || TO_CHAR (i); -
sys.DBMS_SCHEDULER.create_job ( -
job_name => job_name, -
start_date => sysdate+1/1440, -
repeat_interval => 'freq = minutely; interval=1', -
end_date => NULL, -
job_class => 'DEFAULT_JOB_CLASS', -
job_type => 'PLSQL_BLOCK', -
job_action => ' - begin
- INSERT
INTO tb_job -
SELECT ''' -
|| job_name -
|| ''', TO_CHAR (SYSDATE, ''yyyymmdd hh24:mi:ss'') FROM DUAL; - dbms_lock.sleep(60);
- commit;
- end;
- ',
-
enabled => true, -
comments => 'my test job'); -
END LOOP; - END;
- /
-
- SQL>
@add_job -
- PL/SQL
procedure successfully completed. -
- -->查看job_queue_processes参数的值
- SQL>
show parameter job -
- NAME
TYPE VALUE - ------------------------------------
----------- ------------------------------ - job_queue_processes
integer 1 -
- -->此时和Job相关的进程一个,为ora_j000
- SQL>
ho ps -ef | grep rac11g | grep ora_j | grep -v grep - oracle
9692 1 4 12:03 ? 00:00:01 ora_j000_rac11g1 -
- -->查看刚刚添加的job
- -->下面的NEXT_RUN_DATE在秒级别上稍有差异,其实在定义job时,这个是由于前面的for循环造成的延迟
- -->在此忽略这个细微的差异
- SQL>
@jobs -
- OWNER
JOB_NAME ENABL JOB_CLASS NEXT_RUN_DATE - ------------
-------------- ----- ------------------------------ ----------------------------------- - SCOTT
JOB_1 TRUE DEFAULT_JOB_CLASS 19-MAR-13 12.21.33.000000 PM +08:00 - SCOTT
JOB_2 TRUE DEFAULT_JOB_CLASS 19-MAR-13 12.21.33.000000 PM +08:00 - SCOTT
JOB_3 TRUE DEFAULT_JOB_CLASS 19-MAR-13 12.21.33.000000 PM +08:00 - SCOTT
JOB_4 TRUE DEFAULT_JOB_CLASS 19-MAR-13 12.21.34.000000 PM +08:00 - SCOTT
JOB_5 TRUE DEFAULT_JOB_CLASS 19-MAR-13 12.21.34.000000 PM +08:00 -
- -->job执行的情况,可以看到5个job被逐一执行
- -->尽管我们定义时的NEXT_RUN_DATE相差1秒,而此时job的执行后则每一个相差1分钟
- -->job_1与job_5相差4分多钟,这是由于我们定义了dbms_lock.sleep(60)为1分钟
- -->其次可以看出由于只有一个job进程,因此每一个job是一个一个被执行
- SQL>
select * from tb_job; -
- JOB_N
UPDATE_DT - -----
-------------------- - JOB_1
20130319 12:21:33 - JOB_2
20130319 12:22:35 - JOB_3
20130319 12:23:37 - JOB_4
20130319 12:24:39 - JOB_5
20130319 12:25:41
3、测试参数job_queue_processes大于1的情形
- -->首先移除之前的job
- SQL>
ho more remove_job.sql - DECLARE
-
job_name VARCHAR2 (10); - BEGIN
-
FOR i IN 1 .. 5 -
LOOP -
job_name := 'JOB_' || TO_CHAR (i); -
sys.DBMS_SCHEDULER.drop_job (job_name, force => TRUE); -
END LOOP; - END;
- /
-
- SQL>
@remove_job -
- PL/SQL
procedure successfully completed. -
- -->此时设置job_queue_processes的值为6
- SQL>
alter system set job_queue_processes=6; -
- System
altered. -
- -->清空测试用表
- SQL>
truncate table tb_job; -
- Table
truncated. -
- -->此时Oracle为job启动了2个进程
- SQL>
ho ps -ef | grep rac11g | grep ora_j | grep -v grep - oracle
3477 1 9 12:29 ? 00:00:01 ora_j000_rac11g1 - oracle
3491 1 4 12:29 ? 00:00:00 ora_j001_rac11g1 -
- -->添加多个job
- SQL>
@add_job -
- PL/SQL
procedure successfully completed. -
- -->
Author : Robinson - -->
Blog : http://blog.csdn.net/robinson_0612 -
- SQL>
@jobs -
- OWNER
JOB_NAME ENABL JOB_CLASS NEXT_RUN_DATE - --------------------
----------- ----- --------------------- ----------------------------------- - SCOTT
JOB_1 TRUE DEFAULT_JOB_CLASS 19-MAR-13 12.31.55.000000 PM +08:00 - SCOTT
JOB_2 TRUE DEFAULT_JOB_CLASS 19-MAR-13 12.31.56.000000 PM +08:00 - SCOTT
JOB_3 TRUE DEFAULT_JOB_CLASS 19-MAR-13 12.31.56.000000 PM +08:00 - SCOTT
JOB_4 TRUE DEFAULT_JOB_CLASS 19-MAR-13 12.31.56.000000 PM +08:00 - SCOTT
JOB_5 TRUE DEFAULT_JOB_CLASS 19-MAR-13 12.31.56.000000 PM +08:00 -
- -->片刻后可以看到job进程总数达到6个
- SQL>
ho ps -ef | grep rac11g | grep ora_j | grep -v grep - oracle
7668 1 1 11:57 ? 00:00:01 ora_j000_rac11g1 - oracle
7678 1 0 11:57 ? 00:00:01 ora_j001_rac11g1 - oracle
7700 1 1 11:57 ? 00:00:01 ora_j002_rac11g1 - oracle
9230 1 0 11:57 ? 00:00:00 ora_j003_rac11g1 - oracle
9257 1 2 11:58 ? 00:00:01 ora_j005_rac11g1 - oracle
9353 1 7 11:59 ? 00:00:00 ora_j004_rac11g1 -
- -->查看表tb_job的情形
- SQL>
select * from tb_job order by 1,2; -
- JOB_N
UPDATE_DT - -----
-------------------- - JOB_1
20130319 12:31:57 - JOB_1
20130319 12:32:58 - JOB_1
20130319 12:33:59 - JOB_2
20130319 12:31:58 - JOB_2
20130319 12:32:59 - JOB_2
20130319 12:34:00 - JOB_3
20130319 12:31:58 - JOB_3
20130319 12:32:59 - JOB_3
20130319 12:34:00 - JOB_4
20130319 12:31:59 - JOB_4
20130319 12:33:00 - JOB_4
20130319 12:34:01 - JOB_5
20130319 12:31:58 - JOB_5
20130319 12:32:59 - JOB_5
20130319 12:34:00 -
- -->从上面的查询结果可知每一个job的上一次与下一次执行间隔基本保持在1分钟
- -->不同job之间的每一次执行时间基本上是相同的,这与job_queue_processes为1时完全不一样
- -->也就是说即使是job_5,基本上与job_1是同时执行,而不是像前面测试那样前面所有的执行完后才被执行
-
- -->移除job
- SQL>
@remove_job -
- PL/SQL
procedure successfully completed. -
- -->移除测试表
- SQL>
drop table tb_job purge; -
- Table
dropped.
4、小结
a、job_queue_processes参数决定了job作业能够使用的总进程数。
b、当该参数为0值,任何job都不会被执行,建议合理设置该值且至少大于1。
c、对于job运行时间也应该尽量合理的设置间隔以及启动时间。
d、如果同一时间内运行的Job数很多,过小的参数值导致job不得不进行等待。而过大的参数值则消耗更多的系统资源。
f、对于存在依赖关系的job,尽可能将其进行合并到一个job中,如使用chain等。