1

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: enter image description here

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48
Dante
  • 537
  • 2
  • 4
  • 18

1 Answers1

4

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

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48
  • 1
    1) Where do I download these timezone files from? 2) By running, genezi -v, the default seems to be timezone_32.dat. I don't even have this file in the directory, how could this be? – Dante Sep 30 '21 at 02:17
  • 1
    As mentioned, the default is embedded. It's in some kind of virtual file system. You can get updated files from your DBA. I.e. copy the relevant one from a DB install. Oracle supplies updated files with DB patches. – Christopher Jones Sep 30 '21 at 06:14
  • Thank you! That makes so much sense. I'm guessing its in one of those dynamic library (dylib) files. Is there a place where I can simply download the .dat files. I just need timezlrg_26.dat as that is what the server is using. I'm not trying to update the db server (as I have no rights) but downgrade the client to use timezlrg_26.dat. I am unable to use queries like "AT TIME ZONE " without this. I'm curious if this would even work. I may need to downgrade the whole client to 12c. Hopefully it works with the newer version of macOS. – Dante Sep 30 '21 at 13:52
  • I've not seen a separate download place; you could ask Oracle Support. Older instant clients were not certified on the latest macOS, and were not signed, so you may have do some fiddling if you want to go down that route. – Christopher Jones Sep 30 '21 at 22:29