Geek Answers Handbook

How to assign Oracle dbms_scheduler timezone and DST safely

I am trying to set DBMS_SCHEDULER to work at exactly 1 a.m. on January 1 of each year on Oracle 11g. How to set its attributes to be absolutely sure that it will not be executed at the wrong time due to differences in time zone or daylight saving time.

I spent a lot of time processing Oracle documentation, but I still have not reached the level of confidence.

Just btw, here are the rules that I have found and find relevant to the topic:

Job Attributes

start_date This attribute defines the first start date for this job. If start_date and repeat_interval remain empty, then the task is scheduled to start as soon as the task is turned on. To repeat jobs that use a calendar expression to indicate a retry interval, start_date is used as a key date. The first run of the task will be the first match of the calendar expression that is on or after the current date. The scheduler cannot guarantee that the task will be executed at the exact time, because the system can be overloaded and, therefore, resources are not available.

repeat_interval This attribute determines how often the task is repeated. You can specify the retry interval using calendar or PL / SQL expressions. The highlighted expression is evaluated to determine the next run of the job. If repeat_interval is not specified, the task will be executed only once at the specified start date. See "Calendar Syntax" for details.

Calendar Syntax Rules

  • . start_date . , , . , start_date "/" - , . start_date , '-5: 00', , .
  • start_date NULL, :
  • , . : ALTER SESSION, : SQL > ALTER SESSION SET time_zone = '/'; ORA_SDTZ .
  • , DEFAULT_TIMEZONE Scheduler. . SET_SCHEDULER_ATTRIBUTE.
  • DEFAULT_TIMEZONE NULL, systimestamp, .
+7
4

, , , Google, :

start_date      => CAST(trunc(sysdate, 'YEAR')+2/24 AS TIMESTAMP) at time zone 'Europe/Berlin'

, , :

  • - , , DMBS_SCHEDULER default_timezone. , , , .
  • , , , . , SESSIONTIMEZONE DBTIMEZONE .
  • , , , 2 , , max + -2 .

, , , SESSIONTIMEZONE, DBTIMEZONE, start_date Time Zone DBMS_SCHEDULER.

, , - LMT, CET, CEST, CEMT, CEST . - CET (!= ).

+1

, , (/) (: +5: 00). , , DST.

-

declare 
 v_start_date timestamp with time zone;
BEGIN 

select localtimestamp at time zone 'US/Eastern' into v_start_date from dual; --US/Eastern

DBMS_SCHEDULER.CREATE_SCHEDULE(
      schedule_name => 'SAMPLE_SCHEDULE',
      start_date => v_start_date,
      repeat_interval => 'FREQ=DAILY; BYHOUR=10; BYMINUTE= 15',
      comments => 'Runs daily at the specified hour.'); 
END;

, , : ALTER SESSION SET nls_timestamp_tz_format = 'MM-DD-YYYY HH24: MI: SS tzr tzd';

: , , , sysdate start_date, .

- TIMEZONE * USER_SCHEDULER_SCHEDULES;

v1: 27-MAR-14 11.44.24.929282 AM US/EASTERN

v2:

27-MAR-14 05.44.54.000000 PM +05: 00

+2

, , . : * all_scheduler_jobs, owner = 'schema_name'. start_date, timestamp timezone, , : 2017-12-05 01: 55: 00,000000000 EUROPE/YOUR_CITY , . next_run_date start_date .

0

.CREATE_SCHEDULE, .CREATE_JOB start_date, , ,

start_date => TO_TIMESTAMP_TZ('00:10 Europe/Rome','hh24:mi tzr'

dba_scheduler_jobs: "/":

SELECT job_name, TO_CHAR(start_date) start_date,
                 TO_CHAR(next_run_date) next_run_date
    FROM dba_scheduler_jobs;
0

More articles:


All Articles