oracle job使用詳解及job不執行的檢查方法

每天1點執行的oracle 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=;

　　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();), 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=;

^– 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=;

　　^– 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 ;

　　–

　　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