job next_date的计算

今天上一个省检查数据库的时候发现数据库中存在大量job的报错信息:

Sun Jul 29 23:57:11 2007
Errors in file /oracle/app/oracle/admin/oralocal/bdump/oralocal_j000_6297.trc:
ORA-12012: error on auto execute of job 24
ORA-12005: may not schedule automatic refresh for times in the past
Mon Jul 30 23:55:05 2007
Errors in file /oracle/app/oracle/admin/oralocal/bdump/oralocal_j000_7393.trc:
ORA-12012: error on auto execute of job 24
ORA-12005: may not schedule automatic refresh for times in the past
Mon Jul 30 23:57:10 2007
Errors in file /oracle/app/oracle/admin/oralocal/bdump/oralocal_j000_7399.trc:
ORA-12012: error on auto execute of job 24
ORA-12005: may not schedule automatic refresh for times in the past
Tue Jul 31 15:31:48 2007
Thread 1 advanced to log sequence 176
  Current log# 2 seq# 176 mem# 0: /oradata/lfile/redo02.log
Tue Jul 31 23:55:05 2007
Errors in file /oracle/app/oracle/admin/oralocal/bdump/oralocal_j000_8519.trc:
ORA-12012: error on auto execute of job 24
ORA-12005: may not schedule automatic refresh for times in the past
Tue Jul 31 23:57:10 2007
Errors in file /oracle/app/oracle/admin/oralocal/bdump/oralocal_j000_8525.trc:
ORA-12012: error on auto execute of job 24
ORA-12005: may not schedule automatic refresh for times in the past

进一步检查trace文件

/oracle/app/oracle/admin/oralocal/bdump/oralocal_j000_8525.trc
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, Oracle Label Security, 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:    Linux
Node name:      hl-simdb
Release:        2.4.21-20.ELsmp
Version:        #1 SMP Wed Aug 18 20:46:40 EDT 2004
Machine:        i686
Instance name: oralocal
Redo thread mounted by this instance: 1
Oracle process number: 42
Unix process pid: 8525, image: oracle@hl-simdb (J000)

*** SESSION ID:(32.52622) 2007-07-31 23:57:10.272
*** 2007-07-31 23:57:10.272
ORA-12012: error on auto execute of job 24
ORA-12005: may not schedule automatic refresh for times in the past

检查这个job的执行时间:

SQL>select LAST_DATE,LAST_SEC,THIS_DATE,THIS_SEC,NEXT_DATE,NEXT_SEC,INTERVAL from dba_jobs where job=24;
LAST_DATE LAST_SEC         THIS_DATE THIS_SEC         NEXT_DATE NEXT_SEC         INTERVAL
--------- ---------------- --------- ---------------- --------- ---------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
01-AUG-07 00:01:15                                    01-AUG-07 23:55:00         trunc(sysdate)+1435/1440

SQL>

发现其interval是trunc(sysdate)+1435/1440。在这里,说明一下几个字段的具体意思:
(a)LAST_DATE:Date on which this job last successfully executed——job执行成功的时间,即1.job执行成功;2.是job执行完毕的时间
(b)LAST_SEC:同LAST_DATE
(c)THIS_DATE:Date that this job started executing (usually null if not executing)——没有job在执行的时候,该字段为空。若有job正在运行,这个时间是job的开始执行时间。
(d)THIS_SEC:同THIS_DATE
(e)NEXT_DATE:Date that this job will next be executed——job下次执行时间点。
(f)NEXT_SEC:同NEXT_DATE
(g)INTERVAL:A date function, evaluated at the start of execution, becomes next NEXT_DATE——重点的说明一下这个参数,这个参数涉及到next date的计算,next就是根据job的开始执行时间点,结合interval中的时间函数,算出来的。

比如,某个job开始执行时间8月1日23:55,那么如果interval是trunc(sysdate)+1435/1440,因此,next date就是把job的开始时间23:55,代入到interval中的sysdate,得到的结果就仍然是8月1日的23:55。而next date的更新,是一次job完成时更新的,比如job在8月2日的0:15完成,于是在更新next date的时候,就发现next date的时间晚于当前的时间,于是就报错may not schedule automatic refresh for times in the past。

明白了以上的概念后,将interval改成trunc(sysdate+1)+1435/1440问题解决。

begin
  sys.dbms_job.change(job => 24,
                      what => 'P_LOGIN_STATISTICS();',
                      next_date => to_date('2007-08-01 23:55:00', 'yyyy-mm-dd hh24:mi:ss'),
                      interval => 'trunc(sysdate+1)+1435/1440');
  commit;
end;
/

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

This site uses Akismet to reduce spam. Learn how your comment data is processed.