[SOLVED] Where is the Oracle Instant Client timezone file located?

Where is the Oracle Instant Client timezone file located?

I've installed Oracle instant client Version 19.8.0.0.0 on my macOS (Big Sur version 11.5.1) using instructions from the oracle site.

I want to change the time zone settings using ORA_TZFILE file but where do I locate the oracore\zoneinfo\ directory? It is nowhere to be found.

I need to do this because the time zone information between the remote database server and the client do not match hence I am not able to perform queries like:

AT TIME ZONE 'UTC' AS DATE

The remote server is using timezlrg_26.dat. I found this information by doing select * from v$timezone_file. Therefore I need to change the client to also use timezlrg_26.dat.

It ends up giving me the error "ORA-01805: possible error in date/time operation"

This is what the default directory looks like:

Solution

The default timezone files are embedded into the Instant Client libraries, so you won't see them as files on disk.

To change the timezone file used by your version of Instant Client:

create a directory hierarchy instantclient_19_8/oracore/zoneinfo e.g. like mkdir -p instantclient_19_8/oracore/zoneinfo
put the new timezone file in that directory
set an environment variable ORA_TZFILE to the name of the file, e.g. timezone_n.dat. Don't include the path.
You can check which file is being used by running genezi -v

This is discussed in the Oracle Client installation manual Environment Variables for Oracle Instant Client.

Update: from Instant Client 19.18 you can put the timezone file in any directory and use that full path in the environment variable value (Oracle enhancement 31333105).