As a side-effect, sessiontimezone is no more DST (Daylight saving time) aware.
(see Blog: Oracle Datetime (1) - Concepts)
This Blog will provide standalone tests and workaround.
Note 1: Tested in Oracle 19c (19.11).
Note 2: The behaviour has long been observed by other people in Oracle dbms_scheduler/dbms_job applications.
1. dbms_scheduler.enable
-----=========== dbms_scheduler.enable change time_zone ===========-----
declare
l_job_name varchar2(100) := 'TEST_JOB_1';
l_time_zone_orig varchar2(40);
l_time_zone varchar2(40);
begin
execute immediate q'[alter session set time_zone = 'Europe/Paris']';
l_time_zone_orig := sessiontimezone;
dbms_output.put_line('----- 1 sessiontimezone = '|| sessiontimezone);
dbms_scheduler.create_job(
job_name => l_job_name
,job_type => 'PLSQL_BLOCK'
,job_action =>
'begin
dbms_session.sleep(300);
end;'
,start_date => systimestamp
,repeat_interval => 'systimestamp' -- without repeat_interval, not reproducible
,enabled => false
,auto_drop => false);
dbms_output.put_line('----- 2 sessiontimezone = '|| sessiontimezone);
dbms_scheduler.enable(name => l_job_name);
l_time_zone := sessiontimezone;
-- Workaround
if (l_time_zone != l_time_zone_orig) then
dbms_output.put_line('----- 3 sessiontimezone = '|| sessiontimezone);
dbms_output.put_line('--******* Changed after dbms_scheduler.enable. Restore to Original: '|| l_time_zone_orig);
execute immediate 'alter session set time_zone = ''' || l_time_zone_orig || '''';
end if;
dbms_output.put_line('----- 4 sessiontimezone = '|| sessiontimezone);
dbms_scheduler.stop_job (l_job_name, force => true, commit_semantics =>'ABSORB_ERRORS');
dbms_scheduler.disable(name => l_job_name);
dbms_scheduler.drop_job(l_job_name, force => true, commit_semantics =>'ABSORB_ERRORS');
dbms_output.put_line('----- 5 sessiontimezone = '|| sessiontimezone);
end;
/
-----=========== Test Output ===========-----
----- 1 sessiontimezone = Europe/Paris
----- 2 sessiontimezone = Europe/Paris
----- 3 sessiontimezone = +02:00
--******* Changed after dbms_scheduler.enable. Restore to Original: Europe/Paris
----- 4 sessiontimezone = Europe/Paris
----- 5 sessiontimezone = Europe/Paris
2. dbms_scheduler.set_attribute
-----=========== dbms_scheduler.set_attribute change time_zone ===========-----
declare
l_job_name varchar2(100) := 'TEST_JOB_2';
begin
execute immediate q'[alter session set time_zone = 'Europe/Paris']';
dbms_output.put_line('----- 1 sessiontimezone = '|| sessiontimezone);
dbms_scheduler.create_job(
job_name => l_job_name
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN dbms_session.sleep(30); END;'
,start_date => systimestamp
,repeat_interval => 'systimestamp' -- without repeat_interval, not reproducible
,enabled => true
,auto_drop => false);
dbms_output.put_line('----- 2 sessiontimezone = '|| sessiontimezone);
end;
/
declare
l_job_name varchar2(100) := 'TEST_JOB_2'; --'AVQ$AAA_BGP_911_1';
l_time_zone_orig varchar2(40);
l_time_zone varchar2(40);
BEGIN
dbms_session.sleep(5);
l_time_zone_orig := sessiontimezone;
dbms_scheduler.stop_job (l_job_name, force => true, commit_semantics =>'ABSORB_ERRORS');
dbms_output.put_line('----- 3 sessiontimezone = '|| sessiontimezone);
dbms_scheduler.set_attribute(
name => l_job_name
,attribute => 'job_action'
,value => 'BEGIN dbms_session.sleep(60); END;'
);
l_time_zone := sessiontimezone;
-- Workaround
if (l_time_zone != l_time_zone_orig) then
dbms_output.put_line('----- 4 sessiontimezone = '|| sessiontimezone);
dbms_output.put_line('--******* Changed after dbms_scheduler.set_attribute. Restore to Original: '|| l_time_zone_orig);
execute immediate 'alter session set time_zone = ''' || l_time_zone_orig || '''';
end if;
dbms_output.put_line('----- 5 sessiontimezone = '|| sessiontimezone);
dbms_scheduler.stop_job (l_job_name, force => true, commit_semantics =>'ABSORB_ERRORS');
dbms_scheduler.disable(name => l_job_name);
dbms_scheduler.drop_job(l_job_name, force => true, commit_semantics =>'ABSORB_ERRORS');
dbms_output.put_line('----- 6 sessiontimezone = '|| sessiontimezone);
end;
/
-----=========== Test Output ===========-----
----- 1 sessiontimezone = Europe/Paris
----- 2 sessiontimezone = Europe/Paris
----- 3 sessiontimezone = Europe/Paris
----- 4 sessiontimezone = +02:00
--******* Changed after dbms_scheduler.set_attribute. Restore to Original: Europe/Paris
----- 5 sessiontimezone = Europe/Paris
----- 6 sessiontimezone = Europe/Paris
3. dbms_job.change
-----=========== dbms_job.change change time_zone ===========-----
declare
l_job_id pls_integer;
l_time_zone_orig varchar2(40);
l_time_zone varchar2(40);
begin
execute immediate q'[alter session set time_zone = 'Europe/Paris']';
l_time_zone_orig := sessiontimezone;
dbms_output.put_line('----- 1 sessiontimezone = '|| sessiontimezone);
dbms_job.submit(
job => l_job_id,
what => 'BEGIN dbms_session.sleep(30); END;',
next_date => sysdate,
interval => 'sysdate+1/1440'
);
commit;
dbms_output.put_line('--******* l_job_id=' || l_job_id);
--exception when others then dbms_output.put_line('error l_job_id =' || l_job_id); raise;
dbms_output.put_line('----- 2 sessiontimezone = '|| sessiontimezone);
dbms_job.change (
job => l_job_id,
what => 'BEGIN dbms_session.sleep(60); END;',
next_date => sysdate,
interval => 'sysdate+1/1440');
commit;
l_time_zone := sessiontimezone;
-- Workaround
if (l_time_zone != l_time_zone_orig) then
dbms_output.put_line('----- 3 sessiontimezone = '|| sessiontimezone);
dbms_output.put_line('--******* Changed after dbms_job.change. Restore to Original: '|| l_time_zone_orig);
execute immediate 'alter session set time_zone = ''' || l_time_zone_orig || '''';
end if;
dbms_output.put_line('----- 4 sessiontimezone = '|| sessiontimezone);
dbms_job.remove(l_job_id);
commit;
dbms_output.put_line('----- 5 sessiontimezone = '|| sessiontimezone);
end;
/
-----=========== Test Output ===========-----
----- 1 sessiontimezone = Europe/Paris
--******* l_job_id=86009
----- 2 sessiontimezone = Europe/Paris
----- 3 sessiontimezone = +02:00
--******* Changed after dbms_job.change. Restore to Original: Europe/Paris
----- 4 sessiontimezone = Europe/Paris
----- 5 sessiontimezone = Europe/Paris
4. Discussions
In all above three tests, we give a workaround to restore the original sessiontimezone.
We also tried to toggle dbms_scheduler attribute: 'default_timezone', there is no effect.
begin
dbms_scheduler.set_scheduler_attribute(
attribute => 'default_timezone',
value => 'Europe/Paris');
end;
/
select dbms_scheduler.stime from dual;
-- 06-OCT-21 11.34.36.520960000 AM EUROPE/PARIS
begin
dbms_scheduler.set_scheduler_attribute(
attribute => 'default_timezone',
value => '02:00');
end;
/
select dbms_scheduler.stime from dual;
-- 06-OCT-21 11.40.06.230467000 AM +02:00
It seems that session parameter modifications, for example:
alter session set time_zone = 'CET';
alter session set sql_trace=true;
alter session set sql_trace=false;
MOS Note "DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained (Doc ID 467722.1)" has a detail description of Job starting time in connection with Timezone setting in format of Named TZ (TZR) and Offset TZ (TZH:TZM).
