Tuesday, October 12, 2021

Oracle sessiontimezone Format Changed After Calling dbms_scheduler/dbms_job Subprograms

dbms_scheduler/dbms_job subprograms implicitly change sessiontimezone from Named TZ (TZR) to Offset TZ (TZH:TZM) format.
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 looks like that sessiontimezone is gotten by Oracle subroutine "pesstz", and dbtimezone by "pesdbtz".

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;
are related to "kzctxhset".

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).