Question: What is the tns_admin
parameter and how do I use tns_admin to define a common
location for my TNS files?
The tns_admin parameter determines the
location of the tns administration files (e.g. tnsnames.ora and
The tns_admin parameter
is super useful on servers with many databases on different release
levels (hence different $ORACLE_HOME directories) because all
databases tns_admin values can be set to a single location,
allowing a single set of SQL*Net files for all databases on the
Without the tns_admin variable, each instance would have
it's own set of TNS files in the $ORACLE_HOME/network/admin
Search path steps with tns_admin
The tns_admin defines a list of
directories to be searched to find the tnsnames.ora files.
SQL*Net configuration files are resolved in this precedence:
1 - Search for SQL*Net files in present working directory (PWD/CWD)
2 - Set tns_admin for each session or by a
3 - Set tns_admin as a global
environment variable (in kshrc, cshrc, bashrc, etc.)
tns_admin is defined in the registry (In Windows only)
- Search the default location for SQL*Net files in the
$ORACLE_HOME/network/admin directory location (default).
There should be only one tnsnames.ora per server, no matter how
many instances reside on it!
Using tns_admin in Windows
In windows, look for the tns_admin registry entry. If
tns_admin is set then you should be able to use a network
alias, not the full "(DESCRIPTION..." connect string. The TNS_ADMIN
parm tells Oracle clients where to find the tnsnames.ora file.
Also, make sure that tns_admin is set in your DOS
C:> set TNS_ADMIN=$PATH;ORACLE_HOME\network\admin
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.