This discussion is archived
3 Replies Latest reply: Dec 9, 2012 5:23 AM by spajdy RSS

changing the timing in scheduler jobs

user13364377 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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.

Legend

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