Re: dbms_scheduler_job_run_details Question
Date: Wed, 26 Jan 2022 12:16:49 +0100
Message-ID: <CA+S=qd3b7BdXC0JUTNC-ZgCeW_upx9xqEL2du8d6jQaFGwtufw_at_mail.gmail.com>
Hi, Scott
As mentioned by Andy, the columns (although named *_DATE) are not DATE datatype, they are timestamps with time zone.
Logging is in the database time zone, start dates are in the time zone specified for each specific job.
Example:
Database is running GMT:
select dbtimezone from dual;
DBTIME
+00:00
The job was created using an original start date in Europe/Amsterdam:
select
to_char(start_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as start_date
from dba_scheduler_jobs
where job_name = 'MYJOB';
START_DATE
2021-11-25 15:15:00.000 EUROPE/AMSTERDAM The time zones of the job run details show database time zone for LOG_DATE, job time zone for *_START_DATE:
select
to_char(log_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as log_date
, to_char(req_start_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as
req_start_date
, to_char(actual_start_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as
actual_start_date
from dba_scheduler_job_run_details
where job_name = 'MYJOB'
order by log_date desc fetch first 1 row only;
LOG_DATE REQ_START_DATE
ACTUAL_START_DATE
--------------------------------------------------------
--------------------------------------------------------
--------------------------------------------------------
2022-01-26 10:30:07.863 +00:00 2022-01-26
11:30:00.673 EUROPE/AMSTERDAM 2022-01-26 11:30:00.800
EUROPE/AMSTERDAM
Amsterdam is currently GMT+1, so we see that the job actually started at
10:30:00.800 GMT and logged when it ended at 10:30:07.863 GMT.
If you want to display output of job run details in DB time zone alone (to make it easier for humans to compare), you can use the AT TIME ZONE syntax, like for example this:
select
to_char(log_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as log_date
, to_char(req_start_date AT TIME ZONE DBTIMEZONE, 'YYYY-MM-DD
HH24:MI:SS.FF3 TZR') as req_start_date_db_tz
, to_char(actual_start_date AT TIME ZONE DBTIMEZONE, 'YYYY-MM-DD
HH24:MI:SS.FF3 TZR') as actual_start_date_db_tz
from dba_scheduler_job_run_details
where job_name = 'MYJOB'
order by log_date desc fetch first 1 row only;
LOG_DATE
REQ_START_DATE_DB_TZ
ACTUAL_START_DATE_DB_TZ
2022-01-26 10:30:07.863 +00:00 2022-01-26 10:30:00.673 +00:00 2022-01-26 10:30:00.800+00:00
Notice also, that in a case like this, where DB is running GMT, the time difference for Amsterdam is currently 1 hour - when Daylight Savings Time kick in, the time difference will be 2 hours. That means also, that if you want jobs to follow DST, you must not use a DATE when you create jobs if your DB time zone is not a DST aware time zone. The safest way to create jobs is to use a timestamp with explicit time zone, like for example:
begin
dbms_scheduler.create_job (
job_name => 'MYJOB'
, job_type => 'PLSQL_BLOCK'
, job_action => 'mypackage.myproc(42);'
, start_date => TIMESTAMP '2020-11-06 01:30:00
EUROPE/AMSTERDAM'
, repeat_interval => 'freq=daily; byhour=1; byminute=30;'
, job_class => 'MY_JOB_CLASS'
, enabled => false
, auto_drop => false
, comments => 'Job to do something every night at 01:30'
);
end;
/
This will ensure the job runs at 01:30 Amsterdam time, which will be GMT+1
in the winter and GMT+2 in the summer.
If I had used a DATE for the START_DATE parameter, it would have been
converted to a timestamp in DB timezone, which might be a non-DST aware
time zone.
On the other hand if I always want a job to start a given time GMT no matter what time of year it is and ignore DST, then I could just use:
, start_date => TIMESTAMP '2020-11-06 01:30:00 GMT'
Again specifying exactly what I want a job creation, rather than relying on DB time zone.
I hope that helps ;-)
Cheerio
/Kim
Regards
Kim Berg Hansen
Senior Consultant at Trivadis - Part of Accenture
Oracle ACE Director
Author of Practical Oracle SQL
<https://www.apress.com/gp/book/9781484256169>
http://www.kibeha.dk
kibeha_at_kibeha.dk
_at_kibeha <http://twitter.com/kibeha>
On Tue, Jan 25, 2022 at 9:44 PM Scott Canaan <srcdco_at_rit.edu> wrote:
> I got an inquiry from a customer about the dates in the > dbms_scheduler_job_run_details view. He is confused as to why the log_date > is an hour earlier than the req_start_date and actual_start_date. For > example: > > > > Log_id log_date > Job_name > requested_start_date actual_start_date > > 8610130 1/25/2022 4:31:05 AM > RIT_K0704_BL_GOALS_DBMS_JOB 1/25/2022 5:30:00 AM > 1/25/2022 5:30:02 AM > > > > The requested start time is set in the job definition to be 5:30am. I’m > having trouble explaining why there’s a difference. He has asked me to > “fix the GMT differential”. > > > > This is an Oracle 12.1.0.2 database running on Red Hat 7. > > > > *Scott Canaan ‘88* > > *Sr Database Administrator *Information & Technology Services > Finance & Administration > > > *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520 > > *srcdco_at_rit.edu <srcdco_at_rit.edu>* | c: (585) 339-8659 > > *CONFIDENTIALITY NOTE*: The information transmitted, including > attachments, is intended only for the person(s) or entity to which it is > addressed and may contain confidential and/or privileged material. Any > review, retransmission, dissemination or other use of, or taking of any > action in reliance upon this information by persons or entities other than > the intended recipient is prohibited. If you received this in error, please > contact the sender and destroy any copies of this information. > > >
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jan 26 2022 - 12:16:49 CET