Scheduling Jobs
Thursday, July 9th, 2009All my databases run in Greenwich Mean Time, yet I create scheduled jobs for them from my PC running in Australia. If I don’t watch out, a job I thought I’d scheduled for 2AM kicks off at about 4PM London time -because the 2AM I’d specified gets treated as 2AM Australian (my local!) time.
To avoid this, always specify the correct time zone for your start date when scheduling a job:
begin
dbms_scheduler.create_job
(job_name=>'HJR.UPDATE_SOME_MATERIALIZEDVIEWS',
job_type=>'PLSQL_BLOCK',
job_action=>'begin dbms_mview.refresh(''HJR.IMPORTANT_MVIEW'');end;',
start_date=>to_timestamp_tz('08-JUL-2009 02:00:00 +GMT','DD-MON-YYYY HH24:MI:SS TZR'),
repeat_interval=>'FREQ=DAILY; BYHOUR=2; BYMINUTE=00',
enabled=>TRUE);
end;
The important line in this respect is the START_DATE one. That says start at 2AM, but make that 2AM GMT. No matter in which country I find myself when I create this job, therefore, the thing will always kick off at 2 o’clock in the morning, London time (give or take British Summer Time, I suppose!)