3 Replies Latest reply: Dec 9, 2012 7:23 AM by spajdy RSS

    changing the timing in scheduler jobs

    user13364377
      Hi All,

      In the below code I have given the execution timing as 2:15. But next run date value is showing as 3:00 AM. What is wrong here?. I created this job at 2:00 AM

      SQL> select owner,LAST_START_DATE,NEXT_RUN_DATE from dba_Scheduler_jobs where JOB_NAME='job2' and owner='owner1';

      OWNER LAST_START_DATE NEXT_RUN_DATE
      -------------------- -------------------- ------------------------------
      owner1 08-DEC-12 03.00.00.000000 AM -
      05:00


      SQL> BEGIN
      2 DBMS_SCHEDULER.create_job (
      3 job_name => owner1.job2',
      4 job_type => 'PLSQL_BLOCK',
      5 job_action => job2();',
      6 start_date => '08-Dec-12 2.15.00AM',
      7 repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
      8 end_date => NULL,
      9 enabled => TRUE,
      10 comments => 'None');
      11 END;
      12 /
        • 1. Re: changing the timing in scheduler jobs
          user639304
          Hi,

          The output of your query
          select owner,LAST_START_DATE,NEXT_RUN_DATE from dba_Scheduler_jobs where JOB_NAME='job2' and owner='owner1';
          is difficult to read. In this output, what is the value of LAST_START_DATE and what is the value of NEXT_RUN_DATE?
          • 2. Re: changing the timing in scheduler jobs
            Martin Preiss
            the start_date defines the point in time when the schedule becames active. But the time of the next execution is determined by the repeat_interval - in your case: byminute=0; bysecond=0:
            BEGIN
              DBMS_SCHEDULER.create_job (
              job_name => 'test_job',
              job_type => 'PLSQL_BLOCK',
              job_action => 'begin null; end;',
              start_date => to_date('08.12.2012 02:15', 'dd.mm.yyyy hh24:mi'),
              repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
              end_date => NULL,
              enabled => TRUE,
              comments => 'None');
            END;
            /
            
            select job_name
                 , start_date
                 , repeat_interval
                 , next_run_date
              from dba_scheduler_jobs
             where job_name like '%&job_name%'
               and owner not like 'SYS%'
             order by owner
                    , job_name
            
            JOB_NAME   START_DATE                          REPEAT_INTERVAL                      NEXT_RUN_DATE
            ---------- ----------------------------------- ------------------------------------ -------------------------------
            TEST_JOB   08.12.12 02:15:00,000000 +01:00     freq=hourly; byminute=0; bysecond=0; 08.12.12 12:00:00,000000 +01:00
            • 3. Re: changing the timing in scheduler jobs
              spajdy
              Your repeat interval say to scheduler to run every hour.
              Start date is 08.12.2012 02:15 What's the next hour ? It is 08.12.2012 03:00.