ORA-12005: may not schedule automatic refresh for times in the past

Symptoms:

File: alert_orcl.log

Wed Dec 23 16:44:04 2009
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_20112.trc:
ORA-12012: error on auto execute of job 23
ORA-12005: may not schedule automatic refresh for times in the past

File: orcl_j000_20112.trc

Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_20112.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      rh4.ncs.com.sg
Release:        2.6.18-128.1.10.el5
Version:        #1 SMP Thu May 7 10:39:21 EDT 2009
Machine:        i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 20112, image: oracle@rh4.ncs.com.sg (J000)


*** 2009-12-23 16:44:04.666
*** SESSION ID:(19.444) 2009-12-23 16:44:04.666
*** CLIENT ID:() 2009-12-23 16:44:04.666
*** SERVICE NAME:(SYS$USERS) 2009-12-23 16:44:04.666
*** MODULE NAME:() 2009-12-23 16:44:04.666
*** ACTION NAME:() 2009-12-23 16:44:04.666

ORA-12012: error on auto execute of job 23
ORA-12005: may not schedule automatic refresh for times in the past

Root Causes:

Next schedule based on interval evaluate to "past time" due to wrong interval formula.

Wrong formula to calculate 30 minutes interval:

trunc(SYSDATE,'HH')+30/60/24

Correct formular to calculate 30 minutes interval:

trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/30)+1)*30)/(24*60)

Solutions:

SQL> exec dbms_job.interval(job=>23,interval=>'trunc(sysdate,''HH24'')+((floor(to_number(to_char(sysdate,''MI''))/30)+1)*30)/(24*60)');

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS';

Session altered.

SQL> select next_date,interval from dba_jobs where job=23;

NEXT_DATE
--------------------
INTERVAL
---------------------------------------------------------------------------------
2009-DEC-23 17:00:04
trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/30)+1)*30)/(24*60)