This discussion is archived
5 Replies Latest reply: May 11, 2008 10:24 PM by dxbdba RSS

ORA-23420: interval must evaluate to a time in the future

dxbdba Newbie
Currently Being Moderated
Hi to All,

While creating matrelized view im facing this error.Can any one help me on this.

ORA-23420: interval must evaluate to a time in the future
ORA-06512: at "SYS.DBMS_JOB", line 57
ORA-06512: at "SYS.DBMS_JOB", line 134
ORA-06512: at "SYS.DBMS_IREFRESH", line 186
ORA-06512: at "SYS.DBMS_IREFRESH", line 369
ORA-06512: at "SYS.DBMS_REFRESH", line 88
ORA-06512: at "SYS.DBMS_REFRESH", line 62
ORA-06512: at "SYS.DBMS_ISNAPSHOT", line 76
ORA-06512: at line 1


Thanks
  • 1. Re: ORA-23420: interval must evaluate to a time in the future
    sgalaxy Journeyer
    Currently Being Moderated
    To which extent to help you on...??? The error message is clear.... isn't it....??? What have you written trying to create the mv...????

    Greetings....
    Sim
  • 2. Re: ORA-23420: interval must evaluate to a time in the future
    dxbdba Newbie
    Currently Being Moderated
    Hi Sim,

    Here is the script

    CREATE MATERIALIZED VIEW DNRNEW. DNR_IM_CSS
    NOCACHE
    LOGGING
    NOCOMPRESS
    NOPARALLEL
    BUILD IMMEDIATE
    REFRESH FORCE
    START WITH TO_DATE('12-May-2008','dd-mon-yyyy')
    NEXT trunc(sysdate) - to_number(to_char(sysdate - 1, 'd')) + 7
    WITH ROWID
    USING DEFAULT LOCAL ROLLBACK SEGMENT
    AS
    /* Formatted on 2008/05/11 13:25 (Formatter Plus v4.8.7) */
    SELECT *
    FROM IM_CSS@DB1.world;

    thanks
  • 3. Re: ORA-23420: interval must evaluate to a time in the future
    561093 Oracle ACE
    Currently Being Moderated
    I was able to create a test MV using your script without any issues:
    SQL> CREATE MATERIALIZED VIEW mv_test
      2  NOCACHE
      3  LOGGING
      4  NOCOMPRESS
      5  NOPARALLEL
      6  BUILD IMMEDIATE
      7  REFRESH FORCE
      8  START WITH TO_DATE('12-May-2008','dd-mon-yyyy')
      9  NEXT trunc(sysdate) - to_number(to_char(sysdate - 1, 'd')) + 7
    10  WITH ROWID
    11  USING DEFAULT LOCAL ROLLBACK SEGMENT
    12  AS
    13  SELECT *
    14  FROM t;

    Materialized view created.

    SQL>
  • 4. Re: ORA-23420: interval must evaluate to a time in the future
    John Spencer Oracle ACE
    Currently Being Moderated
    You NEXT clause is seriously dependent on the date and time you run the create statement. For me, you want the next run of the job to start almost 15 hours ago.
    SQL> ALTER SESSION SET nls_date_format = 'dd-mon-yy hh24:mi:ss';

    Session altered.

    SQL> SELECT TO_DATE('12-May-2008','dd-mon-yyyy') startdt,
      2         TRUNC(sysdate) - TO_NUMBER(TO_CHAR(sysdate - 1, 'd')) + 7 next_dt,
      3         sysdate now
      4  FROM dual;

    STARTDT            NEXT_DT            NOW
    ------------------ ------------------ ------------------
    12-may-08 00:00:00 11-may-08 00:00:00 11-may-08 14:57:32
    Since I'm not sure when exactly you want to run this next time, you need to play wth your formula.

    John
  • 5. Re: ORA-23420: interval must evaluate to a time in the future
    dxbdba Newbie
    Currently Being Moderated
    Hi Johan,

    If i want to run this matrelized view weekly at 9 pm the what should i give it.

    Thanks and Regards
    Mohd Imran