This discussion is archived
3 Replies Latest reply: Nov 29, 2012 6:56 AM by EdStevens RSS

Side effect of dbms_scheduler.set_attribute_null - setting START_DATE

EdStevens Guru
Currently Being Moderated
Oracle 11.2.0.1.0 SE-One 64-bit
Oracle Linux 5.6 64-bit

Background:
Working to clear what had been an ongoing issue with the DST change. The developers use Quest's SQL Navigator to maintain their scheduled jobs. Unfortunately, SQLNav always sets timezone to GMT offset instead of named time zone. As result, job scheduling is not DST aware and there are a lot of issues every time we cross a change. SQLNav does this every time it touches a job def, so I can't just do a one-time fix.

I discovered that if I run dbms_scheduler.set_attribute_null - setting the start-date, that the specified job would be rescheduled, using the scheduler default time zone - which we have specified as a named time zone. So I set up a schduled job to do that. Actually, the procedure I wrote takes a schema name as an input argument, and I plan to create a seperate scheduled job for each schema we need to keep up on on - I don't want to touch the jobs that are owned by Oracle - EXFSYS, ORACLE_OCM, etc.

Now to the issue at hand. In preliminary tests against one particular schema, the START_DATE was changed to the current date/time, and the timezone of START_DATE was changed from GMT offset to name TZ. In addition, the TZ of NEXT_RUN_DATE was changed from GMT offset to named TZ. This was all exactly as I wanted.

But now I've run it against a schema with many more jobs, and on all but two of his jobs, START_DATE went completely null. Also, NEXT_RUN_DATE was changed to the current date/time.

So, I'm seeing inconsistent side effects of applying dbms_scheduler.set_attribute_null to a job's START_DATE. I'm not so much concerned about the inconsistency I see on START_DATE itself (though it would be nice to understand it), but the complete recalculation of NEXT_RUN_DATE is very problematic.
  • 1. Re: Side effect of dbms_scheduler.set_attribute_null - setting START_DATE
    EdStevens Guru
    Currently Being Moderated
    Here's some more detail and demo. As Pogo (or was it his sidekick Albert?) used to say, "This just gets curiorser and curiorser".

    Oracle 11.2.0.1 SE-One, 64-bit
    Oracle Linux 5.6 64-bit

    Still trying to make sense of some scheduler issues to resolve DST change problems.
    Most of our jobs are created with start_date time zone as a GMT offset, so we know they are not DST aware. Preliminary testing indicated that if I set START_DATE to null (exec dbms_scheduler.set_attribute_null) it would reset the TZ to the default TZ. And it all worked exactly as expected if the jobs use the calendar syntax for the repeat interval. But if the job uses a PL/sql function (as most of them do), we get very strange and seemingly inconsistent results.

    Full disclosure - the system used for the results presented here is running under VirtualBox. To 'accelerate' the time between steps, I shut down the server, changed the time on the host OS, then restarted the server.


    For my test I used this function, from our prod database
    create or replace 
    FUNCTION scott.weekly ( 
        time_of_day
        in
        number,
       
        week_day in string default 'MONDAY')
      
        return date
      
    is
    --
    l_date date;
      
    --
    begin
          
      l_date := trunc(next_day(sysdate,week_day))+time_of_day;
    
          
      return l_date;
      
    end weekly;
     
    So I create 4 jobs, using the 4 combinations of GMT vs. TZ and calendar vs function. For ease of tracking, the jobs are named to reflect the TZ spec and repeat-interval type
    SQL> select systimestamp from dual;
    
    SYSTIMESTAMP
    ---------------------------------------------------------------------------
    06-NOV-12 09.04.03.837190 AM -06:00
    
    SQL> BEGIN
      2  dbms_scheduler.create_job(
      3  job_name => 'SCOTT.JOB_TZ_FNC',
      4  job_type => 'PLSQL_BLOCK',
      5  job_action => 'begin
      6       NULL;
      7  end;',
      8  repeat_interval => 'WEEKLY(.9573)',
      9  start_date => to_timestamp_tz('2012-11-01 America/Chicago', 'YYYY-MM-DD TZR'),
     10  job_class => '"DEFAULT_JOB_CLASS"',
     11  auto_drop => FALSE,
     12  enabled => TRUE);
     13  END;
     14  /
    
    PL/SQL procedure successfully completed.
    
    SQL> --
    SQL> BEGIN
      2  dbms_scheduler.create_job(
      3  job_name => 'SCOTT.JOB_GMT_FNC',
      4  job_type => 'PLSQL_BLOCK',
      5  job_action => 'begin
      6       NULL;
      7  end;',
      8  repeat_interval => 'WEEKLY(.9573)',
      9  start_date => to_timestamp_tz('2012-11-01 -06:00', 'YYYY-MM-DD TZR'),
     10  job_class => '"DEFAULT_JOB_CLASS"',
     11  auto_drop => FALSE,
     12  enabled => TRUE);
     13  END;
     14  /
    
    PL/SQL procedure successfully completed.
    
    SQL> --
    SQL> --
    SQL> BEGIN
      2  dbms_scheduler.create_job(
      3  job_name => 'SCOTT.JOB_TZ_CAL',
      4  job_type => 'PLSQL_BLOCK',
      5  job_action => 'begin
      6       NULL;
      7  end;',
      8  repeat_interval => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=10;BYMINUTE=0;BYSECOND=0',
      9  start_date => to_timestamp_tz('2012-11-01 America/Chicago', 'YYYY-MM-DD TZR'),
     10  job_class => '"DEFAULT_JOB_CLASS"',
     11  auto_drop => FALSE,
     12  enabled => TRUE);
     13  END;
     14  /
    
    PL/SQL procedure successfully completed.
    
    SQL> --
    SQL> BEGIN
      2  dbms_scheduler.create_job(
      3  job_name => 'SCOTT.JOB_GMT_CAL',
      4  job_type => 'PLSQL_BLOCK',
      5  job_action => 'begin
      6       NULL;
      7  end;',
      8  repeat_interval => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=10;BYMINUTE=0;BYSECOND=0',
      9  start_date => to_timestamp_tz('2012-11-01 -06:00', 'YYYY-MM-DD TZR'),
     10  job_class => '"DEFAULT_JOB_CLASS"',
     11  auto_drop => FALSE,
     12  enabled => TRUE);
     13  END;
     14  /
    
    PL/SQL procedure successfully completed.
    
    SQL> --
    SQL> select job_name
      2  ,          start_date
      3  ,          last_start_date
      4  ,          next_run_date
      5  --,     repeat_interval
      6  from dba_scheduler_jobs
      7  where owner='SCOTT'
      8  order by owner,
      9            job_name
     10  ;
    
    JOB_NAME     START_DATE                               LAST_START_DATE                          NEXT_RUN_DATE
    ------------ ---------------------------------------- ---------------------------------------- ----------------------------------------
    JOB_GMT_CAL  01-NOV-12 12.00.00.000000 AM -06:00                                               12-NOV-12 10.00.00.900000 AM -06:00
    JOB_GMT_FNC  01-NOV-12 12.00.00.000000 AM -06:00                                               06-NOV-12 09.04.06.853882 AM -06:00
    JOB_TZ_CAL   01-NOV-12 12.00.00.000000 AM AMERICA/CHI                                          12-NOV-12 10.00.00.900000 AM AMERICA/CHI
                 CAGO                                                                              CAGO
    
    JOB_TZ_FNC   01-NOV-12 12.00.00.000000 AM AMERICA/CHI                                          06-NOV-12 09.04.06.779875 AM AMERICA/CHI
                 CAGO                                                                              CAGO
    
    SQL> select systimestamp from dual;
    
    SYSTIMESTAMP
    ---------------------------------------------------------------------------
    06-NOV-12 09.04.07.130992 AM -06:00
    First anomoly - given that the function WEEKLY defaults to MONDAY, I am surprised that the initial NEXT_RUN is 'now' - which by the system clock is a Tuesday

    After waitining about 30 seconds to give the scheudler a chance to do anything with those jobs scheduled for 'now' ...
    L> --
    SQL> select systimestamp from dual;
    
    SYSTIMESTAMP
    ---------------------------------------------------------------------------
    06-NOV-12 09.04.35.463583 AM -06:00
    
    SQL> --
    SQL> --
    SQL> SELECT job_name
      2  ,          start_date
      3  ,          last_start_date
      4  ,          next_run_date
      5  --,     repeat_interval
      6  from dba_scheduler_jobs
      7  where owner='SCOTT'
      8  order by owner,
      9            job_name
     10  ;
    
    JOB_NAME     START_DATE                               LAST_START_DATE                          NEXT_RUN_DATE
    ------------ ---------------------------------------- ---------------------------------------- ----------------------------------------
    JOB_GMT_CAL  01-NOV-12 12.00.00.000000 AM -06:00                                               12-NOV-12 10.00.00.900000 AM -06:00
    JOB_GMT_FNC  01-NOV-12 12.00.00.000000 AM -06:00      06-NOV-12 09.04.10.781210 AM -06:00      12-NOV-12 10.58.31.000000 PM -06:00
    JOB_TZ_CAL   01-NOV-12 12.00.00.000000 AM AMERICA/CHI                                          12-NOV-12 10.00.00.900000 AM AMERICA/CHI
                 CAGO                                                                              CAGO
    
    JOB_TZ_FNC   01-NOV-12 12.00.00.000000 AM AMERICA/CHI 06-NOV-12 09.04.10.709172 AM AMERICA/CHI 12-NOV-12 10.58.31.000000 PM -06:00
                 CAGO                                     CAGO
    
    
    SQL> --
    SQL> select job_name,
      2            log_id,
      3            log_date,
      4            status
      5  from dba_scheduler_job_log
      6  where owner='SCOTT'
      7  order by job_name,
      8            log_id,
      9            log_date
     10  ;
    
    JOB_NAME         LOG_ID LOG_DATE                                 STATUS
    ------------ ---------- ---------------------------------------- ------------------------------
    JOB_GMT_FNC        1119 06-NOV-12 09.04.11.033583 AM -06:00      SUCCEEDED
    JOB_TZ_FNC         1118 06-NOV-12 09.04.11.000809 AM -06:00      SUCCEEDED
    
    SQL> --
    SQL> select systimestamp from dual;
    
    SYSTIMESTAMP
    ---------------------------------------------------------------------------
    06-NOV-12 09.04.35.601605 AM -06:00
    
    SQL> --
    SQL> spo off
    As I suspected, he fired off the two 'FNC' jobs ... but look what happend to the NEXT_RUN on JOB_TZ_FNC. the TZ changed from a named TZ TO a GMT offset!

    Pressing on, let's apply my proposed fix. To simulate doing this in a system that is up and running and all jobs already with some run history, here is where I run the server forward a week .. to one day after the currently schedule NEXT_RUN for all the jobs.
    SQL> --
    SQL> select systimestamp from dual;
    
    SYSTIMESTAMP
    ---------------------------------------------------------------------------
    13-NOV-12 09.05.56.646719 AM -06:00
    
    SQL> --
    SQL> select job_name
      2  ,          start_date
      3  ,          last_start_date
      4  ,          next_run_date
      5  --,     repeat_interval
      6  from dba_scheduler_jobs
      7  where owner='SCOTT'
      8  order by owner,
      9            job_name
     10  ;
    
    JOB_NAME     START_DATE                               LAST_START_DATE                          NEXT_RUN_DATE
    ------------ ---------------------------------------- ---------------------------------------- ----------------------------------------
    JOB_GMT_CAL  01-NOV-12 12.00.00.000000 AM -06:00      13-NOV-12 09.03.49.002918 AM -06:00      19-NOV-12 10.00.00.400000 AM -06:00
    JOB_GMT_FNC  01-NOV-12 12.00.00.000000 AM -06:00      13-NOV-12 09.03.49.000156 AM -06:00      19-NOV-12 10.58.31.000000 PM -06:00
    JOB_TZ_CAL   01-NOV-12 12.00.00.000000 AM AMERICA/CHI 13-NOV-12 09.03.49.000881 AM AMERICA/CHI 19-NOV-12 10.00.00.400000 AM AMERICA/CHI
                 CAGO                                     CAGO                                     CAGO
    
    JOB_TZ_FNC   01-NOV-12 12.00.00.000000 AM AMERICA/CHI 13-NOV-12 09.03.49.015905 AM -06:00      19-NOV-12 10.58.31.000000 PM -06:00
                 CAGO
    
    
    SQL> --
    SQL> select job_name,
      2            log_id,
      3            log_date,
      4            status
      5  from dba_scheduler_job_log
      6  where owner='SCOTT'
      7  order by job_name,
      8            log_id,
      9            log_date
     10  ;
    
    JOB_NAME         LOG_ID LOG_DATE                                 STATUS
    ------------ ---------- ---------------------------------------- ------------------------------
    JOB_GMT_CAL        1138 13-NOV-12 09.03.49.702137 AM -06:00      SUCCEEDED
    JOB_GMT_FNC        1119 06-NOV-12 09.04.11.033583 AM -06:00      SUCCEEDED
    JOB_GMT_FNC        1141 13-NOV-12 09.03.50.317099 AM -06:00      SUCCEEDED
    JOB_TZ_CAL         1139 13-NOV-12 09.03.50.007371 AM -06:00      SUCCEEDED
    JOB_TZ_FNC         1118 06-NOV-12 09.04.11.000809 AM -06:00      SUCCEEDED
    JOB_TZ_FNC         1140 13-NOV-12 09.03.50.249782 AM -06:00      SUCCEEDED
    
    6 rows selected.
    Ok, as expected, as soon as the server came back up on "tuesday, 13 nov", he caught up on all 'overdue' jobs.

    Now let's apply the fix ... continueing in the same script
    SQL> --
    SQL> --
    SQL> exec dbms_scheduler.disable('SCOTT.JOB_TZ_FNC');
    
    PL/SQL procedure successfully completed.
    
    SQL> exec dbms_scheduler.set_attribute_null('SCOTT.JOB_TZ_FNC','START_DATE');
    
    PL/SQL procedure successfully completed.
    
    SQL> exec dbms_scheduler.enable('SCOTT.JOB_TZ_FNC');
    
    PL/SQL procedure successfully completed.
    
    SQL> --
    SQL> exec dbms_scheduler.disable('SCOTT.JOB_GMT_FNC');
    
    PL/SQL procedure successfully completed.
    
    SQL> exec dbms_scheduler.set_attribute_null('SCOTT.JOB_GMT_FNC','START_DATE');
    
    PL/SQL procedure successfully completed.
    
    SQL> exec dbms_scheduler.enable('SCOTT.JOB_GMT_FNC');
    
    PL/SQL procedure successfully completed.
    
    SQL> --
    SQL> exec dbms_scheduler.disable('SCOTT.JOB_TZ_CAL');
    
    PL/SQL procedure successfully completed.
    
    SQL> exec dbms_scheduler.set_attribute_null('SCOTT.JOB_TZ_CAL','START_DATE');
    
    PL/SQL procedure successfully completed.
    
    SQL> exec dbms_scheduler.enable('SCOTT.JOB_TZ_CAL');
    
    PL/SQL procedure successfully completed.
    
    SQL> --
    SQL> exec dbms_scheduler.disable('SCOTT.JOB_GMT_CAL');
    
    PL/SQL procedure successfully completed.
    
    SQL> exec dbms_scheduler.set_attribute_null('SCOTT.JOB_GMT_CAL','START_DATE');
    
    PL/SQL procedure successfully completed.
    
    SQL> exec dbms_scheduler.enable('SCOTT.JOB_GMT_CAL');
    
    PL/SQL procedure successfully completed.
    
    SQL> --
    SQL> SELECT job_name
      2  ,          start_date
      3  ,          last_start_date
      4  ,          next_run_date
      5  --,     repeat_interval
      6  from dba_scheduler_jobs
      7  where owner='SCOTT'
      8  order by owner,
      9            job_name
     10  ;
    
    JOB_NAME     START_DATE                               LAST_START_DATE                          NEXT_RUN_DATE
    ------------ ---------------------------------------- ---------------------------------------- ----------------------------------------
    JOB_GMT_CAL  13-NOV-12 09.05.57.674335 AM CST6CDT     13-NOV-12 09.03.49.002918 AM -06:00      19-NOV-12 10.00.00.700000 AM CST6CDT
    JOB_GMT_FNC                                           13-NOV-12 09.03.49.000156 AM -06:00      13-NOV-12 09.05.57.559826 AM CST6CDT
    JOB_TZ_CAL   13-NOV-12 09.05.57.631900 AM CST6CDT     13-NOV-12 09.03.49.000881 AM AMERICA/CHI 19-NOV-12 10.00.00.600000 AM CST6CDT
                                                          CAGO
    
    JOB_TZ_FNC                                            13-NOV-12 09.05.57.532243 AM CST6CDT     19-NOV-12 10.58.31.000000 PM -06:00
    Notice several things:
    First, the jobs scheduled with pl/sql functions got a START_DATE of NULL, while those scheduled with calander syntax simply had the START_DATE reset to current date and the scheduler default timezone.
    Second, regarding NEXT_RUN, the jobs scheduled with calander syntax had the date/time left alone, with only the TZ changing to the scheduler default TZ. Of the jobs scheduled with pl/sql function, one was left alone while the other rescheduled to run 'now'.

    And immediately follwing, in the same script
    I'm about to the point of saying we cannot fix the jobs for DST until we first replace the use of the pl/sql functions with standard calendaring.
    SQL> --
    SQL> select job_name,
      2            log_id,
      3            log_date,
      4            status
      5  from dba_scheduler_job_log
      6  where owner='SCOTT'
      7  order by job_name,
      8            log_id,
      9            log_date
     10  ;
    
    JOB_NAME         LOG_ID LOG_DATE                                 STATUS
    ------------ ---------- ---------------------------------------- ------------------------------
    JOB_GMT_CAL        1138 13-NOV-12 09.03.49.702137 AM -06:00      SUCCEEDED
    JOB_GMT_FNC        1119 06-NOV-12 09.04.11.033583 AM -06:00      SUCCEEDED
    JOB_GMT_FNC        1141 13-NOV-12 09.03.50.317099 AM -06:00      SUCCEEDED
    JOB_TZ_CAL         1139 13-NOV-12 09.03.50.007371 AM -06:00      SUCCEEDED
    JOB_TZ_FNC         1118 06-NOV-12 09.04.11.000809 AM -06:00      SUCCEEDED
    JOB_TZ_FNC         1140 13-NOV-12 09.03.50.249782 AM -06:00      SUCCEEDED
    JOB_TZ_FNC         1142 13-NOV-12 09.05.57.545361 AM -06:00      SUCCEEDED
    
    7 rows selected.
    
    SQL> --
    SQL> select systimestamp from dual;
    
    SYSTIMESTAMP
    ---------------------------------------------------------------------------
    13-NOV-12 09.05.57.714671 AM -06:00
    
    SQL> --
    SQL> spo off
  • 2. Re: Side effect of dbms_scheduler.set_attribute_null - setting START_DATE
    spajdy Pro
    Currently Being Moderated
    First anomoly - given that the function WEEKLY defaults to MONDAY, I am surprised that the initial NEXT_RUN is 'now' - which by the system clock is a Tuesday
    That is logical.
    Old fashion jobs has only next_date. When you define job using dbms_job you specify next_date.
    So try to make a little mental experiment how probably ORACLE guys do they work.
    We need to make Scheduler as a new engine for DB jobs.
    1/ This engime must run old job in same fasion as they are runned, because migration must be easy.
    2/ new scheduling via CALENDAr syntax - more human readable, can follow DST
    3/ chain job support
    4/ event based job support

    to point 1/ - OK it is trivial we copy API of dbms_job.submit
    to point 2/ - hmm, how we get next_run_date from CALENDAR expression ?
    What about to define start_date and then we will evaluate CALENDAR expression to find first date after start_date acording this expression ?
    Good idea but we must rename parameter of our dbms_scheduler.creaste_job from next_run_date to start_date.
    When user call this function and repeat_interval is in PL/SQL syntax simply copy start_date to next_run_date.
    When user call this function and repeat_interval is in CALENDAR syntax get next_run_date evaluating repeat_interval formula related to start_date.
    DECLARE
      v_start_date TIMESTAMP WITH TIME ZONE := to_timestamp_tz('2012-11-01 America/Chicago', 'YYYY-MM-DD TZR');
      v_next_run_date TIMESTAMP WITH TIME ZONE;
    BEGIN  
     dbms_scheduler.evaluate_calendar_string(calendar_string => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=10;BYMINUTE=0;BYSECOND=0',start_date => v_start_date,return_date_after => v_start_date,next_run_date => v_next_run_date);
     dbms_output.put_line(to_char(v_next_run_date));
    END;
    /
    But what if start_date is NULL ?
    Use actual system date.
    As I suspected, he fired off the two 'FNC' jobs ... but look what happend to the NEXT_RUN on JOB_TZ_FNC. the TZ changed from a named TZ TO a GMT offset!
    I explain it to you in this thread: DST questions, part duex
    First, the jobs scheduled with pl/sql functions got a START_DATE of NULL, while those scheduled with calander syntax simply had the START_DATE reset to current date and the scheduler default timezone.
    Second, regarding NEXT_RUN, the jobs scheduled with calander syntax had the date/time left alone, with only the TZ changing to the scheduler default TZ. Of the jobs scheduled with pl/sql function, one was left alone while the other rescheduled to run 'now'.
    >
    You don't simply set start_date to NULL. You also disable and enable jobs.
    Try to set start_date to NULL when jobs are enabled.
    And second variant disable and enable job only.
    I suppose that when job is enabled then next_run_date is recaclulated in some case.

    And last
    I'm about to the point of saying we cannot fix the jobs for DST until we first replace the use of the pl/sql functions with standard calendaring.
    Yes this is correct, the same thing say ORACLE Doc:

    Repeating jobs with frequencies smaller than daily follow their frequencies exactly across daylight savings adjustments. For example, suppose that a job is scheduled to repeat every 3 hours, the clock is moved forward from 1:00 a.m. to 2:00 a.m., and the last time the job ran was midnight. Its next scheduled time will be 4:00 a.m. Thus, the 3 hour period between subsequent job runs is retained. The same applies when the clock is moved back. This behavior is not the case for repeating jobs that have frequencies of daily or larger. For example, if a repeating job is supposed to be executed on a daily basis at midnight, it will continue to run at midnight if the clock is moved forward or backward. When the execution time of such a daily (or larger frequency) job happens to fall inside a window where the clock is moved forward, the job executes at the end of the window.


    The calendaring syntax does not allow you to specify a time zone. Instead the Scheduler retrieves the time zone from the start_date argument. If jobs must follow daylight savings adjustments you must make sure that you specify a region name for the time zone of the start_date. For example specifying the start_date time zone as 'US/Eastern' in New York will make sure that daylight saving adjustments are automatically applied. If instead the time zone of the start_date is set to an absolute offset, such as '-5:00', daylight savings adjustments are not followed and your job execution will be off by an hour half of the year.


    When start_date is NULL, the Scheduler will determine the time zone for the repeat interval as follows:
    1.
    It will check whether the session time zone is a region name. The session time zone can be set by either:

    Issuing an ALTER SESSION statement, for example:
    SQL> ALTER SESSION SET time_zone = 'Asia/Shanghai';


    Setting the ORA_SDTZ environment variable.


    2.
    If the session time zone is an absolute offset instead of a region name, the Scheduler will use the value of the DEFAULT_TIMEZONE Scheduler attribute. For more information, see the SET_SCHEDULER_ATTRIBUTE Procedure.

    3.
    If the DEFAULT_TIMEZONE attribute is NULL, the Scheduler will use the time zone of systimestamp when the job or window is enabled.
  • 3. Re: Side effect of dbms_scheduler.set_attribute_null - setting START_DATE
    EdStevens Guru
    Currently Being Moderated
    spajdy wrote:
    First anomoly - given that the function WEEKLY defaults to MONDAY, I am surprised that the initial NEXT_RUN is 'now' - which by the system clock is a Tuesday
    That is logical.
    Old fashion jobs has only next_date. When you define job using dbms_job you specify next_date.
    So try to make a little mental experiment how probably ORACLE guys do they work.
    We need to make Scheduler as a new engine for DB jobs.
    1/ This engime must run old job in same fasion as they are runned, because migration must be easy.
    2/ new scheduling via CALENDAr syntax - more human readable, can follow DST
    3/ chain job support
    4/ event based job support

    to point 1/ - OK it is trivial we copy API of dbms_job.submit
    to point 2/ - hmm, how we get next_run_date from CALENDAR expression ?
    What about to define start_date and then we will evaluate CALENDAR expression to find first date after start_date acording this expression ?
    Good idea but we must rename parameter of our dbms_scheduler.creaste_job from next_run_date to start_date.
    When user call this function and repeat_interval is in PL/SQL syntax simply copy start_date to next_run_date.
    When user call this function and repeat_interval is in CALENDAR syntax get next_run_date evaluating repeat_interval formula related to start_date.
    DECLARE
    v_start_date TIMESTAMP WITH TIME ZONE := to_timestamp_tz('2012-11-01 America/Chicago', 'YYYY-MM-DD TZR');
    v_next_run_date TIMESTAMP WITH TIME ZONE;
    BEGIN  
    dbms_scheduler.evaluate_calendar_string(calendar_string => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=10;BYMINUTE=0;BYSECOND=0',start_date => v_start_date,return_date_after => v_start_date,next_run_date => v_next_run_date);
    dbms_output.put_line(to_char(v_next_run_date));
    END;
    /
    Good info and insight.
    But what if start_date is NULL ?
    Use actual system date.
    As I suspected, he fired off the two 'FNC' jobs ... but look what happend to the NEXT_RUN on JOB_TZ_FNC. the TZ changed from a named TZ TO a GMT offset!
    I explain it to you in this thread: DST questions, part duex
    First, the jobs scheduled with pl/sql functions got a START_DATE of NULL, while those scheduled with calander syntax simply had the START_DATE reset to current date and the scheduler default timezone.
    Second, regarding NEXT_RUN, the jobs scheduled with calander syntax had the date/time left alone, with only the TZ changing to the scheduler default TZ. Of the jobs scheduled with pl/sql function, one was left alone while the other rescheduled to run 'now'.
    >
    You don't simply set start_date to NULL. You also disable and enable jobs.
    Try to set start_date to NULL when jobs are enabled.
    And second variant disable and enable job only.
    I suppose that when job is enabled then next_run_date is recaclulated in some case.
    Ok, just reran the tests both ways, and it seems the operative variable was the disable/enable. First test was resetting START_DATE without the disable/enable. No change from previous tests that included them. Second test was to simply disable/enable without resetting START_DATE. Again, no change from previous tests.

    - Edit: well, no change in the behavior of the jobs themselves, the function based jobs continued to get rescheduled and run immediately whenever they are touched. But without the disable/enable pair, the reset of START_DATE treated the function based the same as the calendar based - START_DATE had the TZ info changed, but was not actually set to NULL.

    Yep, for safety sake, I'm going to work through converting all jobs to calendaring and get rid of the functions. THEN it will be safe to set up a recurring job to fix anything that gets created/modified with a GMT offse -- which the developer's favorite tool does.

    >
    And last
    I'm about to the point of saying we cannot fix the jobs for DST until we first replace the use of the pl/sql functions with standard calendaring.
    Yes this is correct, the same thing say ORACLE Doc:

    Repeating jobs with frequencies smaller than daily follow their frequencies exactly across daylight savings adjustments. For example, suppose that a job is scheduled to repeat every 3 hours, the clock is moved forward from 1:00 a.m. to 2:00 a.m., and the last time the job ran was midnight. Its next scheduled time will be 4:00 a.m. Thus, the 3 hour period between subsequent job runs is retained. The same applies when the clock is moved back. This behavior is not the case for repeating jobs that have frequencies of daily or larger. For example, if a repeating job is supposed to be executed on a daily basis at midnight, it will continue to run at midnight if the clock is moved forward or backward. When the execution time of such a daily (or larger frequency) job happens to fall inside a window where the clock is moved forward, the job executes at the end of the window.


    The calendaring syntax does not allow you to specify a time zone. Instead the Scheduler retrieves the time zone from the start_date argument. If jobs must follow daylight savings adjustments you must make sure that you specify a region name for the time zone of the start_date. For example specifying the start_date time zone as 'US/Eastern' in New York will make sure that daylight saving adjustments are automatically applied. If instead the time zone of the start_date is set to an absolute offset, such as '-5:00', daylight savings adjustments are not followed and your job execution will be off by an hour half of the year.


    When start_date is NULL, the Scheduler will determine the time zone for the repeat interval as follows:
    1.
    It will check whether the session time zone is a region name. The session time zone can be set by either:

    Issuing an ALTER SESSION statement, for example:
    SQL> ALTER SESSION SET time_zone = 'Asia/Shanghai';


    Setting the ORA_SDTZ environment variable.


    2.
    If the session time zone is an absolute offset instead of a region name, the Scheduler will use the value of the DEFAULT_TIMEZONE Scheduler attribute. For more information, see the SET_SCHEDULER_ATTRIBUTE Procedure.

    3.
    If the DEFAULT_TIMEZONE attribute is NULL, the Scheduler will use the time zone of systimestamp when the job or window is enabled.
    Edited by: EdStevens on Nov 29, 2012 8:51 AM

Legend

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