How do sessions get their initial timezone

Question

When I check the session timezone in SQL developer with

select sessiontimezone from dual;

I get 'Europe/Berlin'. When I do the same in SQL*Plus I get '+02:00'.

This makes a difference when dealing with TIMESTAMP WITH LOCAL TIMEZONE, as Germany is sometimes in timezone UTC+01 and sometimes in UTC+02 due to daylight saving time. (Currently it's in UTC+02.)

How do the two tools get / set (?) their default timezones?

System information:

• Connections are via TNS.
• SQL Developer: 18.1.0.095.1630
• Oracle DBMS: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
• Operating system server: x86_64/Linux 2.4.xx
• Operating system client: Microsoft Windows 10 Professional

Answer 1

The documentation list only the environment variable ORA_SDTZ.

However, it is taken from following settings:

• Registry Key HKCU\SOFTWARE\ORACLE\KEY_{Oracle Home Name}\ORA_SDTZ

• Registry Key HKLM\SOFTWARE\ORACLE\KEY_{Oracle Home Name}\ORA_SDTZ

  (resp. HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_{Oracle Home Name}\ORA_SDTZ)

• Environment variable ORA_SDTZ

• Current locale settings of your machine (most likely).

• Database time zone if none from above is found (just an assumption)

Your client application may have some additional sources for session time zone, see How to change the timezone of Oracle SQL Developer / Oracle Data Modeler? as example.

Environment variable should have the highest precedence.