I have new request from developers, they would like to have scheduler set in way that sessiontimezone would be possible in named region timezone.
Application uses variable ORA_SDTZ='Europe/Bratislava' (or thay are able to do similiar for JDBC Thin at java level). However the would like to have oracle scheduler jobs also using named regions instead of offsets as well.
oracle:(19.13.0.h1)ESKJJD> echo $ORA_SDTZ
Europe/Bratislava
skedwy99a:/home/oracle/BABO
oracle:(19.13.0.h1)ESKJJD> sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 23 17:49:24 2023
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
Europe/Bratislava
I'm quite surprised it does not seem to work still in 19c. I have read well known MOS document https://support.oracle.com/epmos/faces/DocumentDisplay?id=467722.1 (DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained).
SYS@eskjjd skedwy99a> drop table SYS.SYSTIME_TEST purge;
Table SYS.SYSTIME_TEST dropped.
SYS@eskjjd skedwy99a> create table SYS.SYSTIME_TEST (col1 varchar2(50 char));
Table SYS.SYSTIME_TEST created.
begin
dbms_scheduler.create_job('SYSTIME_TEST_JOB','PLSQL_BLOCK','begin insert into SYS.SYSTIME_TEST select to_char(systimestamp, ''DD/MM/YYYY HH24:MI:SS TZR'') from dual; insert into SYS.SYSTIME_TEST select to_char(sysdate, ''DD/MM/YYYY HH24:MI:SS'') from dual; insert into SYS.SYSTIME_TEST select SESSIONTIMEZONE from dual; commit; end;',start_date=>null, enabled=>true,repeat_interval => null);
end;
/
PL/SQL procedure successfully completed.
exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
SYS@eskjjd skedwy99a> select * from SYS.SYSTIME_TEST;
COL1
_____________________________
23/08/2023 17:39:34 +02:00
23/08/2023 17:39:34
+02:00
I have also read paragraph "The sysdate and systimestamp "seen" by jobs may be different then the one when you connect, the used timezone is the one that was set when the database (!) was started."
However we always use Olsson TZ format on OS level and database has been started with TZ='Europe/Bratislava'
skedwy99a:/home/oracle/BABO
oracle:(19.13.0.h1)ESKJJD> echo $TZ
Europe/Bratislava
oracle:(19.13.0.h1)ESKJJD> ps -ef | grep pmon
oracle 24117744 1 0 13:04:20 - 0:00 ora_pmon_ESKJJD
skedwy99a:/home/oracle/BABO
oracle:(19.13.0.h1)ESKJJD> ps eww 24117744
PID TTY STAT TIME COMMAND
24117744 - A 0:00 ora_pmon_ESKJJD _=/oracle/product/19.13.0.h1/bin/sqlplus TMPDIR=/home/oracle/tmp LANG=C TEMP=/tmp LOGIN=oracle DR_CNTRL=TEXTPSIZE=64K@STACKPSIZE=64K@DATAPSIZE=64K SSH_TTY=/dev/pts/0 CLCMD_PASSTHRU=1 PATH= NLS_LANG=AMERICAN_AMERICA.EE8ISO8859P2 EXTENDED_HISTORY=ON PATH_BACK=/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin:/usr/lbin:/root/MSP/bin:.:/usr/local/bin:/opt/oracle/utils:/opt/oracle ORACLE_BASE=/oracle BCK_PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin:/usr/lbin:/root/MSP/bin:.:/usr/local/bin:/opt/oracle/utils:/opt/oracle LC__FASTMSG=true EXINIT=set noai nosm EDITOR=vi CLASSPATH=/oracle/product/19.13.0.h1/JRE:/oracle/product/19.13.0.h1/jlib:/oracle/product/19.13.0.h1/rdbms/jlib:/oracle/product/19.13.0.h1/network/jlib LOGNAME=oracle TMP=/home/oracle/tmp MAIL=/usr/spool/mail/oracle TEMPDIR=/tmp ORACLE_SID=ESKJJD PS1=\?skedwy99a:$PWD\?$LOGIN:(${ORACLE_HOME##*/})$ORACLE_SID> UTILS_DIR=/opt/oracle/utilities USER=oracle NLS_DATE_FORMAT=DD.MM.YYYY AUTHSTATE=compat TNS_ADMIN=/oracle/product/19.13.0.h1/network/admin ORA_NLS10=/oracle/product/19.13.0.h1/nls/data DISPLAY=10.215.224.194:10 SHELL=/usr/bin/ksh ORA_SDTZ=Europe/Bratislava ORACLE_TERM=xterm ODMDIR=/etc/objrepos CLUSTER_OVERRIDE=yes HOME=/home/oracle SSH_CONNECTION=10.212.232.93 60731 10.212.153.121 22 SSH_CLIENT=10.212.232.93 60731 22 TERM=xterm-256color MAILMSG=[YOU HAVE NEW MAIL] ORACLE_HOME=/oracle/product/19.13.0.h1 PWD=/home/oracle/BABO TZ=Europe/Bratislava AIXTHREAD_SCOPE=S A__z=! LOGNAME ORA_NET2_DESC=8,11 MPROTECT_TXT=ON SKGP_SPAWN_DIAG_POST_FORK_TS= SKGP_HIDDEN_ARGS= SKGP_SPAWN_DIAG_PRE_FORK_TS= SKGP_SPAWN_DIAG_PRE_EXEC_TS= ORACLE_SPAWNED_PROCESS=1 RDMAV_FORK_SAFE=1 RDMAV_HUGEPAGES_SAFE=1 NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat:/usr/lib/nls/msg/%l.%c/%N:/usr/lib/nls/msg/%l.%c/%N.cat LIBPATH=/oracle/product/19.13.0.h1/lib:/oracle/product/19.13.0.h1/network/lib:/lib:/usr/lib:/usr/openwin/lib:/usr/td/lib:/usr/ucblib:/usr/local/lib LD_LIBRARY_PATH=/oracle/product/19.13.0.h1/lib:/oracle/product/19.13.0.h1/network/lib:/lib:/usr/lib:/u