This discussion is archived
8 Replies Latest reply: Jul 23, 2013 8:20 AM by mjsadiq RSS

how to reset next_run_date of a Scheduler job

ozoracle Newbie
Currently Being Moderated
Hi,

How can I reset the next_run_date of a job in the Scheduler?

For example, I have a job which is running every 10 seconds. I want to stop it now and let it run again in let's say an hour. I tried changing the start_date and repeate_interval attributes with no luck.

Oracle 10g R1
MyUser@MyDB> SELECT to_char(NEXT_RUN_DATE, 'dd-Mon hh12:mi:SS am tzr') NEXT_RUN_DATE
  2  FROM DBA_SCHEDULER_JOBS
  3  WHERE job_name='TESTME';

NEXT_RUN_DATE
---------------------------------------------------
20-Jan 11:12:00 AM +11:00

MyUser@MyDB> 
MyUser@MyDB> EXEC DBMS_SCHEDULER.DISABLE('TESTME');

PL/SQL procedure successfully completed.

MyUser@MyDB> EXEC DBMS_SCHEDULER.SET_ATTRIBUTE
('TESTME', 'START_DATE',  TO_TIMESTAMP_TZ('20-01-2
011 11:30:00 AM +11','DD-MM-YYYY HH:MI:SS AM TZR'));

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
MyUser@MyDB> EXEC DBMS_SCHEDULER.SET_ATTRIBUTE
('TESTME', 'REPEAT_INTERVAL',  'FREQ=SECONDLY; INT
ERVAL=10');

PL/SQL procedure successfully completed.

MyUser@MyDB> EXEC DBMS_SCHEDULER.ENABLE('TESTME');

MyUser@MyDB> SELECT to_char(NEXT_RUN_DATE, 'dd-Mon hh12:mi:SS am tzr') 
NEXT_RUN_DATE
  2  FROM DBA_SCHEDULER_JOBS
  3  WHERE job_name='TESTME';

NEXT_RUN_DATE
---------------------------------------------------
20-Jan 11:12:40 AM +11:00     <-- still running, it doesn't wait till 11:30
  • 1. Re: how to reset next_run_date of a Scheduler job
    rp0428 Guru
    Currently Being Moderated
    Do this test:

    Disable the job and then DO NOT alter it at all until you confirm (query next_run_date as you show) that it has been disabled.

    Only then should you change the attributes and reenable the job.

    Of course, the easiest way is to just do a DROP_JOB, monitor until you confirm that the job has been dropped, and just as important, that is is not still executing.

    Then recreate the job.

    A similar issue used to occur with DBMS_JOB when the interval is to short. When the job is removed from the job queue to run the process also adds it back to the queue for the next run. This queue had to be cleared of the job or Oracle wouldl use the queue version and ignore changes you tried to make. This usually only happened if a DBA tried to manually enter the commands; they would often forget to issue the commit. You don't need the commit with scheduler.
  • 2. Re: how to reset next_run_date of a Scheduler job
    CKPT Guru
    Currently Being Moderated
    Once you disable it please check the status and also check the owner column.

    select job_name,owner,enabled from dba_scheduler_jobs where job_name='TESTME';
  • 3. Re: how to reset next_run_date of a Scheduler job
    ozoracle Newbie
    Currently Being Moderated
    This drives me nuts

    I dropped the job and created it again and still the NEXT RUN TIME is not later than the START DATE!!
    MyUser@MYDB> BEGIN
      2   dbms_scheduler.DROP_JOB( 'TESTME' );
      3    DBMS_SCHEDULER.CREATE_JOB
      4      (
      5         job_name        => 'BATCHREP.TESTME'
      6        ,start_date      => TO_TIMESTAMP_TZ('20-01-2011 02:50:00 PM +11','DD-MM-YYYY HH:MI:SS AM
    TZR')
      7        ,repeat_interval => 'FREQ=SECONDLY; INTERVAL=10'
      8        ,end_date        => NULL
      9        ,job_class       => 'DEFAULT_JOB_CLASS'
     10        ,job_type        => 'PLSQL_BLOCK'
     11        ,job_action      => 'begin  INSERT INTO XX VALUES(S.NEXTVAL); COMMIT; end;'
     12        ,comments        => 'TEST'
     13      );
     14   dbms_scheduler.ENABLE( 'TESTME' );
     15  END;
     16  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.02
    MyUser@MYDB> 
    MyUser@MYDB> 
    MyUser@MYDB> SELECT to_char(NEXT_RUN_DATE, 'dd-Mon hh12:mi:SS am tzr') NEXT_RUN_DATE
      2  FROM DBA_SCHEDULER_JOBS
      3  WHERE job_name='TESTME';
    
    NEXT_RUN_DATE
    ---------------------------------------------------
    20-Jan 02:26:40 PM +11:00      <-- what's goiong on!!
  • 4. Re: how to reset next_run_date of a Scheduler job
    ozoracle Newbie
    Currently Being Moderated
    Guys, the whole code is easy... you can test it yourself... the result is illogical !!
    create sequence s;
    
    create table Xx ( a varchar2(100));
    
    BEGIN
     --dbms_scheduler.DROP_JOB( 'TESTME' );
      DBMS_SCHEDULER.CREATE_JOB
        (
           job_name        => 'TESTME'
          ,start_date      => TO_TIMESTAMP_TZ('20-01-2011 03:50:00 PM +11','DD-MM-YYYY HH:MI:SS AM TZR')
          ,repeat_interval => 'FREQ=SECONDLY; INTERVAL=10'
          ,end_date        => NULL
          ,job_class       => 'DEFAULT_JOB_CLASS'
          ,job_type        => 'PLSQL_BLOCK'
          ,job_action      => 'begin  INSERT INTO XX VALUES(S.NEXTVAL); COMMIT; end;'
          ,comments        => 'TEST'
        );
     dbms_scheduler.ENABLE( 'TESTME' );
    END;
    /
    
    SELECT to_char(NEXT_RUN_DATE, 'dd-Mon hh12:mi:SS am tzr') NEXT_RUN_DATE
    FROM DBA_SCHEDULER_JOBS
    WHERE job_name='TESTME';
    
    SELECT MAX(TO_NUMBER(A)) FROM XX;
  • 5. Re: how to reset next_run_date of a Scheduler job
    sb92075 Guru
    Currently Being Moderated
    OzOracle wrote:
    Guys, the whole code is easy... you can test it yourself... the result is illogical !!
    create sequence s;
    
    create table Xx ( a varchar2(100));
    
    BEGIN
    --dbms_scheduler.DROP_JOB( 'TESTME' );
    DBMS_SCHEDULER.CREATE_JOB
    (
    job_name        => 'TESTME'
    ,start_date      => TO_TIMESTAMP_TZ('20-01-2011 03:50:00 PM +11','DD-MM-YYYY HH:MI:SS AM TZR')
    ,repeat_interval => 'FREQ=SECONDLY; INTERVAL=10'
    ,end_date        => NULL
    ,job_class       => 'DEFAULT_JOB_CLASS'
    ,job_type        => 'PLSQL_BLOCK'
    ,job_action      => 'begin  INSERT INTO XX VALUES(S.NEXTVAL); COMMIT; end;'
    ,comments        => 'TEST'
    );
    dbms_scheduler.ENABLE( 'TESTME' );
    END;
    /
    please realize that at this point in time you could issue the following SQL statement

    ROLLBACK;

    so what are the implications for the DML done by DBMS_SCHEDULER.CREATE_JOB procedure?
  • 6. Re: how to reset next_run_date of a Scheduler job
    ozoracle Newbie
    Currently Being Moderated
    Guess what!

    The code worked when I used sysdate function with the start_date parameter.
    It fails when I use TO_TIMESTAMP_TZ function with it.

    Very Odd!!! :-(

    Sorry "sb92075", I don't know what you are speaking about.
    MyUser@MyDB> SELECT SYSDATE FROM DUAL;
    
    SYSDATE
    -------------------
    20-01-2012 03:42:08
    
    MyUser@MyDB> BEGIN
      2    dbms_scheduler.DROP_JOB( 'TESTME' ); 
      3    DBMS_SCHEDULER.CREATE_JOB
      4      (
      5         job_name        => 'TESTME'
      6        ,start_date      => sysdate + 0.02
      7        ,repeat_interval => 'FREQ=MINUTELY; INTERVAL=1'
      8        ,end_date        => NULL
      9        ,job_class       => 'DEFAULT_JOB_CLASS'
     10        ,job_type        => 'PLSQL_BLOCK'
     11        ,job_action      => 'begin  INSERT INTO XX VALUES(S.NEXTVAL); COMMIT; end;'
     12        ,comments        => 'TEST'
     13      );
     14    dbms_scheduler.ENABLE( 'TESTME' );
     15  END;
     16  /
    
    PL/SQL procedure successfully completed.
    
    MyUser@MyDB> 
    MyUser@MyDB> SELECT to_char(NEXT_RUN_DATE, 'dd-Mon hh12:mi:SS am tzr') NEXT_RUN_DATE
      2  FROM DBA_SCHEDULER_JOBS
      3  WHERE job_name='TESTME';
    
    NEXT_RUN_DATE
    ---------------------------------------------------
    20-Jan 04:11:12 PM +11:00
    
    MyUser@MyDB> 
    MyUser@MyDB> 
    MyUser@MyDB> 
    MyUser@MyDB> 
    MyUser@MyDB> BEGIN
      2    dbms_scheduler.DROP_JOB( 'TESTME' ); 
      3    DBMS_SCHEDULER.CREATE_JOB
      4      (
      5         job_name        => 'TESTME'
      6        ,start_date      => TO_TIMESTAMP_TZ('20-01-2011 04:11:00 PM +11','DD-MM-YYYY HH:MI:SS AM
    TZR')
      7        ,repeat_interval => 'FREQ=MINUTELY; INTERVAL=1'
      8        ,end_date        => NULL
      9        ,job_class       => 'DEFAULT_JOB_CLASS'
     10        ,job_type        => 'PLSQL_BLOCK'
     11        ,job_action      => 'begin  INSERT INTO XX VALUES(S.NEXTVAL); COMMIT; end;'
     12        ,comments        => 'TEST'
     13      );
     14    dbms_scheduler.ENABLE( 'TESTME' );
     15  END;
     16  /
    
    PL/SQL procedure successfully completed.
    
    MyUser@MyDB> 
    MyUser@MyDB> 
    MyUser@MyDB> SELECT to_char(NEXT_RUN_DATE, 'dd-Mon hh12:mi:SS am tzr') NEXT_RUN_DATE
      2  FROM DBA_SCHEDULER_JOBS
      3  WHERE job_name='TESTME';
    
    NEXT_RUN_DATE
    ---------------------------------------------------
    20-Jan 03:44:00 PM +11:00
  • 7. Re: how to reset next_run_date of a Scheduler job
    rp0428 Guru
    Currently Being Moderated
    >
    please realize that at this point in time you could issue the following SQL statement
    ROLLBACK;
    so what are the implications for the DML done by DBMS_SCHEDULER.CREATE_JOB procedure?
    >
    The job still exists. With DBMS_SCHEDULER you don't need a COMMIT after a CREATE_JOB it commits implicitly.
  • 8. Re: how to reset next_run_date of a Scheduler job
    mjsadiq Newbie
    Currently Being Moderated

    Hi,

     

    You have to change the 'START_DATE' attribute for that. Following is a sample script.

     

    declare

        v_new_next_date timestamp with time zone;

    begin

        select to_timestamp_tz(to_char(trunc(next_run_date),'DD-MON-YYYY') || ' 9:30:00 PM +00:00') into v_new_next_date

            from user_scheduler_jobs

            where job_name = 'TEST_JOB';

        dbms_scheduler.set_attribute(name=>'TEST_JOB', attribute=>'START_DATE',value=>v_new_next_date);

    end;

    /

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points