Skip to content

Where is TNSNAMES.ORA Location

  • by

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)))"
Connected.

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:

CHICAGO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = chicago-db-server)(PORT = 1521))
    (CONNECT_DATA =
      (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
Connected.

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:

%TNS_ADMIN%\tnsnames.ora

Problem is, there's no TNS_ADMIN environment variable in most database systems by default. However, you can use ORACLE_HOME to derive it.

%ORACLE_HOME%\network\admin\tnsnames.ora

But first of all, you have to know where ORACLE_HOME is in Windows OS before finding the file.

Let's see an example.

tnsnames.ora in Windows

tnsnames.ora in Windows

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:

$ORACLE_HOME/network/admin/tnsnames.ora

Again, you have to know where ORACLE_HOME is in Linux before finding the file.

Leave a Reply

Your email address will not be published.