Home > problem-cause-solution > Oracle Job Scheduler runs my jobs, one hour earlier, (Daylight Saving Time related)

Oracle Job Scheduler runs my jobs, one hour earlier, (Daylight Saving Time related)


Problem:

Scheduler runs my jobs one hour earlier, which causes empty summary tables in ETL process.

Log Date                  2010/01/21 02:00:09.072642 +02:00

Required Start Date  2010/01/21 03:00:04.662096 +03:00

Actual Start Date      2010/01/21 03:00:00.000000 +03:00

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name        => 'MYCHEMA.SR54'
,start_date      => SYSTIMESTAMP,
,repeat_interval => 'FREQ=DAILY;BYHOUR=3;BYMINUTE=0;BYSECOND=0'
,end_date        => NULL
,job_class       => 'DEFAULT_JOB_CLASS'
,job_type        => 'PLSQL_BLOCK'
,job_action      => 'GENERIC_SUMMARY.procprocessreport ( 54 );'
,comments        => 'trunc(sysdate+1)+3/24'
);
SYS.DBMS_SCHEDULER.enable (name => 'log_parallel_process_job');
END;
/

Cause:

Using SYSTIMESTAMP as start date is converted as for example:

start_date      => TO_TIMESTAMP_TZ(‘2009/08/05 15:14:24.499000 +03:00′,’yyyy/mm/dd hh24:mi:ss.ff tzr’)

You can see it from the ddl script of this in TOAD.

Thus, it means that you are hard coding the daylight saving time.

For example in Turkey, DST is GMT +03:00, it means if you create this script in summer time it will be hardcoded as +03:00 and if it is in other than day light saving time(normal time), it will be +02:00 in Turkey.

Solution:

Using named timezone code solves the problem, it will automatically adjust the execution time for you. Do not forget that, it also prevents

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name        => 'MYCHEMA.SR54'
 ,start_date => TO_TIMESTAMP_TZ('2010/01/21 10:00:00.000000 Turkey','yyyy/mm/dd hh24:mi:ss.ff tzr')
,repeat_interval => 'FREQ=DAILY;BYHOUR=3;BYMINUTE=0;BYSECOND=0'
,end_date        => NULL
,job_class       => 'DEFAULT_JOB_CLASS'
,job_type        => 'PLSQL_BLOCK'
,job_action      => 'GENERIC_SUMMARY.procprocessreport ( 54 );'
,comments        => 'trunc(sysdate+1)+3/24'
);

DBMS_SCHEDULER.enable (name => ‘log_parallel_process_job’);

End;

/

For further details, and thanks goes to below links:

http://forums.oracle.com/forums/thread.jspa?threadID=646581

You can find your Time Zone Names in Oracle® Database Globalization Support Guide:

For eample: http://download-uk.oracle.com/docs/cd/B28359_01/server.111/b28298/applocaledata.htm#i637736

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Don Charisma

because anything is possible with Charisma

Carol no Mundo!

Aventuras de uma intercambista a trabalho pelo mundo!

Blog do PHP

Oracle DBA for rookies!

nimaidba

Welcome to the world of Oracle with me....

Tech

News and reviews from the world of gadgets, gear, apps and the web

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Gurcan Orhan's ODI and DWH Blog

Some words about Oracle Data Integrator and Data Warehousing.

%d bloggers like this: