Skip to content

Instantly share code, notes, and snippets.

@jarek-przygodzki
Last active May 18, 2023 22:23
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jarek-przygodzki/cbea3cedae3aef2bbbe0ff6b057e8321 to your computer and use it in GitHub Desktop.
Save jarek-przygodzki/cbea3cedae3aef2bbbe0ff6b057e8321 to your computer and use it in GitHub Desktop.
ORA-01882: timezone region not found

JDBC client sends command to setup session when physical connection is established via a modified AUTH_ALTER_SESSION OCI attribute in the authentication phase.

If the JVM time zone is one of the magic constants in the oracle.sql.ZONEIDMAP class and oracle.jdbc.timezoneAsRegion = true (default) then AUTH_ALTER_SESSION looks like this (where Etc/UTC is the default timezone picked up by the JVM)

ALTER SESSION SET TIME_ZONE='Etc/UTC' NLS_LANGUAGE='POLISH' NLS_TERRITORY='POLAND' 

which can fail with

ORA-01882: timezone region not found
01882. 00000 -  "timezone region not found"

when given timezone id is not supported by server (see V$TIMEZONE_NAMES)

There are several solutions.

The first one is setting oracle.jdbc.timezoneAsRegion = false as the connection parameter or virtual machine property (-Doracle.jdbc.timezoneAsRegion=false). Then the query modifying the session turns to this

ALTER SESSION SET TIME_ZONE='+0:00' NLS_LANGUAGE='POLISH' NLS_TERRITORY='POLAND'

The second solution is to change the JVM time zone with the user.timezone parameter to timezone that is equivalent to what you need and supported by databse e.g. -Duser.timezone=UTC or even -Duser.timezone=Etc/GMT.

Trivia

The V$TIMEZONE_FILE view displays the zone file version being used by the database.

SELECT FILENAME,VERSION from V$TIMEZONE_FILE;
FILENAME                VERSION
-------------------- ----------
timezlrg_14.dat              14

Time zone file can be upgraded: https://oracle-base.com/articles/misc/update-database-time-zone-file

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment