Oracle InstantClient: TNSNAMES.ORA location

When you install the InstantClient binaries, you often want to use your existing TNSNAMES.ORA file containing all your databases. So how do you specify the location of your TNSNAMES.ORA file?

The Oracle website has the answer:

Always set the TNS_ADMIN environment variable or registry to the location of the tnsnames.ora file (full directory path only, do not include the file name). This practice will ensure that you are using the appropriate tnsnames.ora for your application when running with Instant Client.

So, for UNIX and Linux systems set the variable TNS_ADMIN like so in your .profile or .bash_profile:

export TNS_ADMIN=/opt/oracle/instantclient_11_2/

On Windows systems, set the environment variables via the Advanced System properties:

  • Open CMD, enter sysdm.cpl
  • In the Advanced tab, select Environment Variables
  • Under “System Variables”, click on New… and enter “TNS_ADMIN” as the name and the path where your TNSNAMES.ORA resides as your value.

Now, you can use your TNS names for your InstantClient, for example for SQL*Plus:

$ sqlplus simon@KDB01

For more information on SQL*Plus configuration, please refer to the Oracle documentation for SQL*Plus.