How does Toad and SQL Navigator access the tnsnames.ora file and info? How do you change/specify the TNSnames.ora file location?
In the TNSnames Editor in Toad, it automatically picks up the tnsnames.ora file, but I want to redirect to a different file. Is that possible?
Checks Environmental Variables settings for the client info and then checks the registry. Direction to the tnsnames.ora can be set in various areas.
WORKAROUND 1:
Under Control Panel | System | Advanced | Environmental Variables - System Variables section, check that the first path noted in the PATH variable leads to the Oracle client directory that you wish to use. From here Toad will pick up the registry info and be directed to the tnsnames.ora file.
In the registry, the TNS_ADMIN variable for each home will define where the tnsnames.ora file is located. This can be adjusted.
**Note: Back up the current registry before makings changes. Make changes at your own risk as it can affect your overall environment.
1. Go to Start | Run and type regedit.
2. Drill down to HKEY_LOCAL_MACHINE | Software | ORACLE
3. On the right, there should be an entry "TNS_ADMIN" - double-click on it and edit, entering the desired directory path to your TNSNAMES.ora file (i.e. C:\).
4. Click "OK" to apply.
If you have more than one Oracle client installed, expanding the "ORACLE" node should reveal more folders named "OraHome" or similar (for client versions 10 and newer), or note "HOME0", "HOME1", "HOME2", etc. (for client versions 9 and older). These nodes represent the different Oracle clients you have installed. You can add a TNS_ADMIN specifically for each client too (optional if the upper level of ORACLE is already set as noted above).
1. First click on the "HOME#" node or "OraHome" form the left hand side.
2. On the right hand side, right-click and select New | String Value.
3. Name this new entry as 'TNS_ADMIN'.
4. Now double-click 'TNS_ADMIN' and set the value to be the path to your desired file location. Click 'OK' to apply.
Steps 1-4 can be repeated if you have other clients/HOME# nodes.
Once the changes have been made in the restart your computer. When you launch TOAD or SQL Navigator, you should see that it references the TNSNAMES.ora file that you configured in the registry, based off of what client you've selected to connect with in the application's connection window.
WORKAROUND 2:
Another options is to create a TNS_Admin variable, instead of the registry, under System Variables and specify the path of the tnsnames.ora file. This will take precedence over other paths that lead to the tnsnames.ora file. Keep in mind this will affect other installed applications that may be using Oracle and the client.
1. Select Control Panel | System
2. Select Advanced Tab | Environment Variables button
3. Under System variables, look for an existing TNS_ADMIN variable and edit the value. Or create a new variable and enter the following:
NAME field= TNS_ADMIN
VALUE field = <desired path to the TNSNames.ora file>
4. Hit OK to save and exit all windows
Disclaimer : Quest does not provide support for problems that arise from improper modification of the registry. The Windows registry contains information critical to your computer and applications. Make sure you back up the registry before modifying it. For more information on the Windows Registry Editor and how to back up and restore it, refer to Microsoft Article ID 256986 "Description of the Microsoft Windows registry": Link.
This applies to various versions.
