2008-09-25 11:32:25| 分類： 數(shù)據(jù)庫(kù) | 標(biāo)簽：job oracle |字號(hào) 訂閱
最近發(fā)現(xiàn)兩個(gè)數(shù)據(jù)庫(kù)每天晚上23：55分都有警告日志的報(bào)錯(cuò)信息：
Wed Sep 24 23:55:04 2008
Errors in file /oracle/app/oracle/admin/gdimall/bdump/gdimall_j001_4043.trc:
ORA-12012: error on auto execute of job 3
ORA-12005: may not schedule automatic refresh for times in the past
Wed Sep 24 23:57:09 2008
Errors in file /oracle/app/oracle/admin/gdimall/bdump/gdimall_j001_4043.trc:
ORA-12012: error on auto execute of job 3
ORA-12005: may not schedule automatic refresh for times in the past
查看trace文件：
gd_idb01:[/oracle/app/oracle/admin/gdimall/bdump$]cat /oracle/app/oracle/admin/gdimall/bdump/gdimall_j001_4043.trc
/oracle/app/oracle/admin/gdimall/bdump/gdimall_j001_4043.trc
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /oracle/app/oracle/product/9.2.0
System name: HP-UX
Node name: gd_idb01
Release: B.11.11
Version: U
Machine: 9000/800
Instance name: gdimall
Redo thread mounted by this instance: 1
Oracle process number: 147
Unix process pid: 4043, image: oracle@gd_idb01 (J001)
*** SESSION ID:(782.45392) 2008-09-24 23:55:04.144
*** 2008-09-24 23:55:04.144
ORA-12012: error on auto execute of job 3
ORA-12005: may not schedule automatic refresh for times in the past
*** SESSION ID:(782.45400) 2008-09-24 23:57:09.407
*** 2008-09-24 23:57:09.407
ORA-12012: error on auto execute of job 3
ORA-12005: may not schedule automatic refresh for times in the past
查看相關(guān)的job：
SQL>select job,log_user,schema_user,what,LAST_DATE,LAST_SEC,THIS_DATE,THIS_SEC,NEXT_DATE,NEXT_SEC,INTERVAL from dba_jobs where job=3;
JOB LOG_USER SCHEMA_USER
---------- ------------------------------ ------------------------------
WHAT
--------------------------------------------------------------------------------
LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC
--------- ---------------- --------- ---------------- --------- ----------------
INTERVAL
--------------------------------------------------------------------------------
3 COMP_EAF COMP_EAF
P_LOGIN_STATISTICS();
25-SEP-08 00:01:14 25-SEP-08 23:55:00
trunc(sysdate)+1435/1440
發(fā)現(xiàn)interval是trunc(sysdate)+1435/1440。在視圖all_jobs中，以下列的含義為：
（a）LAST_DATE：Date on which this job last successfully executed——job成功執(zhí)行的完畢時(shí)間
（b）LAST_SEC：Same as LAST_DATE. This is when the last successful execution started.－－上次成功執(zhí)行的開始時(shí)間
（c）THIS_DATE：Date that this job started executing (usually null if not executing)——沒有job在執(zhí)行的時(shí)候，該字段為空。若有job正在運(yùn)行，這個(gè)時(shí)間是job的開始執(zhí)行時(shí)間。
（d）THIS_SEC：Same as THIS_DATE. This is when the last successful execution started.
（e）NEXT_DATE：Date that this job will next be executed——job下次執(zhí)行時(shí)間。
（f）NEXT_SEC：Same as NEXT_DATE. This is when the last successful execution started。
（g）INTERVAL：A date function, evaluated at the start of execution, becomes next NEXT_DATE
對(duì)于該INTERVAL參數(shù)如上例：job 3執(zhí)行時(shí)間為9月24日23：55，那么如果interval是trunc(sysdate)+1435/1440，因此，next date就是把job的開始時(shí)間23：55，代入到interval中的sysdate，得到的結(jié)果就仍然是9月24日的23：55。而next date的更新，是一次job完成時(shí)更新的，比如job在9月24日的1：14完成，于是在更新next date的時(shí)候，就發(fā)現(xiàn)next date的時(shí)間晚于當(dāng)前的時(shí)間，于是就報(bào)錯(cuò)may not schedule automatic refresh for times in the past。而事實(shí)上，job應(yīng)該是成功執(zhí)行了的。為了解決這個(gè)報(bào)錯(cuò)，interval改成trunc(sysdate+1)+1435/1440。
conn COMP_EAF/COMP_EAF
begin
sys.dbms_job.change(job => 3,
what => 'P_LOGIN_STATISTICS();',
next_date => to_date('2008-09-25 23:55:00', 'yyyy-mm-dd hh24:mi:ss'),
interval => 'trunc(sysdate+1)+1435/1440');
commit;
end;
/
說明，執(zhí)行的時(shí)候，如果以sys執(zhí)行可以會(huì)報(bào)以下錯(cuò)誤，以all_jobs中shema_user連接執(zhí)行即可。
ERROR at line 1:
ORA-23421: job number 64 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 529
ORA-06512: at "SYS.DBMS_JOB", line 204
ORA-06512: at "SYS.DBMS_JOB", line 185
ORA-06512: at line 2
聯(lián)系客服
