Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



Oracle configuration file tips

Article by author Steve Callan

This article deals with knowing how to set a location for your tnsnames.ora file and where to look for that location (having a "good" file and "accepting" its use because you know where it is or how to find it). The ubiquitous Oracle user name Scott just received a promotion: he is a newly anointed junior DBA who must set up his company's 100 computers to use Oracle. Scott knows he has a good tnsnames.ora file, and he wants to use it as a template for everyone else.

One huge misconception about using Oracle products that occurs among other-than-RDBMS users (novice Forms developers in particular) has to do with the proper configuration of the non-RDBMS Oracle product.

Every Oracle product has its own set of "Oracle network" configuration files. Each Oracle product has its own ORACLE_HOME. Within those ORACLE_HOME locations is a directory named network, and within the network directory, you will find a directory named admin. This is where Oracle installs the Oracle Net configuration files, including tnsnames.ora. Like many other things in Oracle, if Oracle puts it there, Oracle expects to find it there, unless you happen to intervene. In this case, intervention can be a good thing.

Where does Oracle expect to find the tnsnames.ora file? The table below shows some of the possible locations.

UNIX platforms Windows platforms
ORACLE_HOME\network\admin ORACLE_HOME/network/admin
/var/opt/oracle No counterpart
A .tnsnames file in your home directory No counterpart
In the directory specified by the TNS_ADMIN environment variable In the directory specified by the TNS_ADMIN environment variable or registry setting

When Scott needs to add a new database (service identifier, etc.) for employees to use, or change a host name, all he needs to do is edit one file: the tnsnames.ora file found in the directory specified by the TNS_ADMIN variable. The change is immediately available to everyone. UNIX users can place the environment variable in logon or resource files (for example, C-shell users can use "setenv TNS_ADMIN /some/location" in the .cshrc file; other shells can have it exported, and so on). Where do Windows users place or set the variable?

I mentioned the "Registry" word earlier, but only because I wanted to be technically correct. Oracle's documentation even mentions placing the TNS_ADMIN variable in the "R" word location. But really, what possible reason would you want to use the "Registry" for something like that? There is no need to; and, in fact, you can find other documents from Oracle that discourage the use of directly editing the registry. Which, by the way, is good advice, and it happens to match Microsoft's admonition about directly editing the registry. So, the answer for Windows users: set TNS_ADMIN as an environment variable, either at the system or user level.

Suppose you have a user who uses SQL*Plus and Form Builder from Forms 6i. Each product creates its own set of configuration files in their respective ORACLE_HOME-network-admin directories (substitute / or \ for the hyphen, depending on the platform). Simply "direct" both Oracle products to use the tnsnames.ora file found in the directory specified by the TNS_ADMIN environment variable. The solution to the "What gives?" question posted on numerous web sites is that simple.

In closing, the other configuration file frequently used in conjunction with the tnsnames.ora file (sqlnet.ora) can also be placed in the same directory specified by the TNS_ADMIN environment variable. Whether you choose to use sqlnet.ora is a separate issue; there is no general absolute requirement to use it, but there are many cases where it is useful and necessary.







 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks