oracle job使用详解及job不运行的检查方法(转载)

                   oracle job使用详解及job不运行的检查方法

 

每天1点执行的oracle JOB样例

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X,
what => 'ETL_RUN_D_Date;',
next_date => to_date('2009-08-26 01:00:00','yyyy-mm-dd hh24:mi:ss'),
interval => 'trunc(sysdate)+1+1/24',
no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/

以上是明确指定每天的1点执行此job,如果指定是每天中午12点执行interval需要指定为'trunc(sysdate)+1+12/24',如果仅仅指定interval为一天,这样当你手工用dbms_job.run(job)去运行一次时,job每天的执行时间是会改变的,如果你想job每天在固定时间执行,可以参考上面的例子.

 

初始化相关参数job_queue_processes
alter system set job_queue_processes=39 scope=spfile;//最大值不能超过1000 ;job_queue_interval = 10 //调度作业刷新频率秒为单位

 

job_queue_process 表示oracle能够并发的job的数量，可以通过语句　　

show parameter job_queue_process;

来查看oracle中job_queue_process的值。当job_queue_process值为0时表示全部停止oracle的job，可以通过语句

ALTER SYSTEM SET job_queue_processes = 10;

来调整启动oracle的job。

相关视图：
dba_jobs
all_jobs
user_jobs
dba_jobs_running 包含正在运行job相关信息

 

－－－－－－－－－－－－－－－－－－－－－－－－－

提交job语法：

begin
sys.dbms_job.submit(job => :job,
what => 'P_CLEAR_PACKBAL;',
next_date => to_date('04-08-2008 05:44:09', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+ 1/360');
commit;
end;
/

 

－－－－－－－－－－－－－－－－－－－－－－－－－
创建JOB
variable jobno number;
begin
dbms_job.submit(:jobno, 'P_CRED_PLAN;',SYSDATE,'SYSDATE+1/2880',TRUE);
commit;

 

运行JOB
SQL> begin
dbms_job.run(:job1);
end;
/

 

删除JOB
SQL> begin
dbms_job.remove(:job1);
end;
/

 

DBA_JOBS
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
字段（列） 类型 描述
JOB NUMBER 任务的唯一标示号
LOG_USER VARCHAR2(30) 提交任务的用户
PRIV_USER VARCHAR2(30) 赋予任务权限的用户
SCHEMA_USER VARCHAR2(30) 对任务作语法分析的用户模式
LAST_DATE DATE 最后一次成功运行任务的时间
LAST_SEC VARCHAR2(8) 如HH24:MM:SS格式的last_date日期的小时，分钟和秒
THIS_DATE DATE 正在运行任务的开始时间，如果没有运行任务则为null
THIS_SEC VARCHAR2(8) 如HH24:MM:SS格式的this_date日期的小时，分钟和秒
NEXT_DATE DATE 下一次定时运行任务的时间
NEXT_SEC VARCHAR2(8) 如HH24:MM:SS格式的next_date日期的小时，分钟和秒
TOTAL_TIME NUMBER 该任务运行所需要的总时间，单位为秒
BROKEN VARCHAR2(1) 标志参数，Y标示任务中断，以后不会运行
INTERVAL VARCHAR2(200) 用于计算下一运行时间的表达式
FAILURES NUMBER 任务运行连续没有成功的次数
WHAT VARCHAR2(2000) 执行任务的PL/SQL块
CURRENT_SESSION_LABEL RAW MLSLABEL 该任务的信任Oracle会话符
CLEARANCE_HI RAW MLSLABEL 该任务可信任的Oracle最大间隙
CLEARANCE_LO RAW MLSLABEL 该任务可信任的Oracle最小间隙
NLS_ENV VARCHAR2(2000) 任务运行的NLS会话设置
MISC_ENV RAW(32) 任务运行的其他一些会话参数

 

－－－－－－－－－－－－－－－－－－－－－－－－－－
描述 INTERVAL参数值
每天午夜12点 'TRUNC(SYSDATE + 1)'
每天早上8点30分 'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)'
每星期二中午12点 'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'
每个月第一天的午夜12点 'TRUNC(LAST_DAY(SYSDATE ) + 1)'
每个季度最后一天的晚上11点 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'
每星期六和日早上6点10分 'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）'

 

－－－－－－－－－－－－－－－－－－－－－－－－－－

1:每分钟执行

Interval => TRUNC(sysdate,'mi') + 1/ (24*60)

或

Interval => sysdate+1/1440

2:每天定时执行

例如：每天的凌晨1点执行

Interval => TRUNC(sysdate) + 1 +1/ (24)

3:每周定时执行

例如：每周一凌晨1点执行

Interval => TRUNC(next_day(sysdate,'星期一'))+1/24

4:每月定时执行

例如：每月1日凌晨1点执行

Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24

5:每季度定时执行

例如每季度的第一天凌晨1点执行

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

6:每半年定时执行

例如：每年7月1日和1月1日凌晨1点

Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24

7:每年定时执行

例如：每年1月1日凌晨1点执行

Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24

 

JOB不运行的检查步骤：

 

 

ORACLE有一种定时调度机制，用dbms_job包来管理。

　　设置的JOB就是不运行，搞得的郁闷，

　　最好执行了这个才搞定 exec dbms_ijob.set_enabled(true);

　　下面提供一个checklist用于检查job异常的原因：

　　1) Instance in RESTRICTED SESSIONS mode?

　　Check if the instance is in restricted sessions mode:

　　select instance_name,logins from v$instance;

　　If logins=RESTRICTED, then:

　　alter system disable restricted session;

　　^– Checked!

　　2) JOB_QUEUE_PROCESSES=0

　　Make sure that job_queue_processes is > 0

　　show parameter job_queue_processes

　　^– Checked!

　　3) _SYSTEM_TRIG_ENABLED=FALSE

　　Check if _system_enabled_trigger=false

　col parameter format a25

　　col value format a15

　　select a.ksppinm parameter,b.ksppstvl value from x$ksppi a,x$ksppcv b

　　where a.indx=b.indx and ksppinm=’_system_trig_enabled’;

　　If _system_trig_enabled=false, then

　　alter system set “_system_trig_enabled”=TRUE scope=both;

　　^– Checked!

　　4) Is the job BROKEN?

　　select job,broken from dba_jobs where job=<job_number>;

　　If broken, then check the alert log and trace files to diagnose the issue.

　　^– Checked! The job is not broken.

　　5) Is the job COMMITted?

　　Make sure a commit is issued after submitting the job:

BEGIN

　　SYS.DBMS_JOB.SUBMIT

　　(

　　job => X

　　,what => ‘dbms_utility.analyze_schema

　　(”SCOTT”,”COMPUTE”,NULL,NULL,NULL);’

　　,next_date => to_date(’08/06/2005 09:35:00′,’dd/mm/yyyy hh24:mi:ss’)

　　,no_parse => FALSE

　　);

　　COMMIT;

　　END;

　　/

　　If the job executes fine if forced (i.e., exec dbms_jobs.run(<job_no>);), then likely a commit

　　is missing.

　　^– Checked! The job is committed after submission.

　　6) UPTIME > 497 days

　　Check if the server (machine) has been up for more than 497 days:

　　For SUN, use ‘uptime’ OS command.

　　If uptime>497 and the jobs do not execute automatically, then you are hitting unpublished bug 3427424

　　(Jobs may stop running after 497 days uptime) which is fixed in 9206 and A102

　　^– Checked! The server in this case has been up 126 days only

　　7) DBA_JOBS_RUNNING

　　Check dba_jobs_running to see if the job is still running:

　　select * from dba_jobs_running;

　　^– Checked! The job is not running.

　　LAST_DATE and NEXT_DATE

　　Check if the last_date and next_date for the job are proper:

　　select Job,Next_date,Last_date from dba_jobs where job=<job_number>;

^– NEXT_DATE is porper, however LAST_DATE is null since the job never executes automatically.

9) NEXT_DATE and INTERVAL

　　Check if the Next_date is changing properly as per the interval set in dba_jobs:

　　select Job,Interval,Next_date,Last_date from dba_jobs where job=<job_number>;

　　^– This is not possible since the job never gets executed automatically.

　　10) Toggle value for JOB_QUEUE_PROCESSES

　　Stop and restart CJQ process(es)

　　alter system set job_queue_processes=0 ;

　　–<Wait for some time to ensure CJQ process stopped>

　　alter system set job_queue_processes=4 ;

　　Ref: Bug 2649244 (fixed by: 9015, 9203, 10201)

　　^– Done but did not help

　　11) DBMS_IJOB(Non-documented):

　　Last ditch effort.

　　Either restart the database or try the following:

　　exec dbms_ijob.set_enabled(true);

　　Ref: Bug 3505718 (Closed, Not a Bug)

　　Done but did not help

　　These are the most common causes for this behavior.

　　Solution

　　The solution ended up to be the server (machine) uptime.

　　Even though it was up for only 126 days, after the server was rebooted all jobs were able to execute automatically.

　　To implement the solution, please execute the following steps:

　　1. Shutdown all applications, including databases.

　　2. Shutdown the server (machine)

　　3. Restart all applications, including databases.

　　4. Check that jobs are executing automatically.

　　from metalink docs ： 313102.1

补充一示例(脚本)：

begin
  sys.dbms_job.submit(job => :job,
                      what => 'PA_TRS.sp_trs_selfupdatedailytime();',
                      next_date => to_date('14-02-2011 09:10:16', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'TRUNC(SYSDATE + 1) + (9*60+10)/(24*60)');
  commit;
end;
--定时每天上午9:10执行一存储过程

 

(引自：http://guyuanli.itpub.net/post/37743/484763)