What is TNSNAMES?
Whenever we need to connect to the database, we usually provide a connection string like this:
SQL> conn hr/hr@chicago-db-server:1521/ORCL Connected.
Or we can provide the full connect description.
SQL> conn hr/hr@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=chicago-db-server)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)))"
As we can see, the connection string looks lengthy and error prone. Luckily, we have Oracle Net Local Naming that can simplify it.
Local naming is an alias system conducted by tnsnames.ora file, each alias is a connect identifier which represents a long, complex connection description including destination, port, service name, SID, etc.
An example of connect identifier would look like this:
(ADDRESS = (PROTOCOL = TCP)(HOST = chicago-db-server)(PORT = 1521))
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
In the above, CHICAGO, the connect identifier represents the connection details behind the equals sign. That is to say, you don't have to use full connect description to connect to the database, you can use the alias directly. Connection tools like SQL*Plus will lookup its content in tnsnames.ora at real-time. For example:
SQL> conn hr/hr@chicago
It's simple and clean.
Where is tnsnames.ora?
Now the question is, where to find tnsnames.ora?
1. tnsnames.ora in Windows OS
For Windows OS, the location of tnsnames.ora file is usually at:
Problem is, there's no TNS_ADMIN environment variable in most database systems by default. However, you can use ORACLE_HOME to derive it.
But first of all, you have to know where ORACLE_HOME is in Windows OS before finding the file.
Let's see an example.
If there's no tnsnames.ora, please copy one in sample folder for yourself.
2. tnsnames.ora in Linux and Unix-liked OS
For Linux and Unix-like OS, the location of tnsnames.ora file is at:
Again, you have to know where ORACLE_HOME is in Linux before finding the file.