8 Replies Latest reply: Jul 23, 2013 10:20 AM by mjsadiq RSS

    how to reset next_run_date of a Scheduler job

    ozoracle
      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
          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
            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
              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
                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
                  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
                    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
                      >
                      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

                        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;

                        /