TNSNAMES.ORA File - The Complete Guide

Apr 10, 2017 · 8 min read · sql ·

The TNSNAMES.ORA file is an important file when working with Oracle. Learn all about what it is, its location, and how to create and edit it in this article.

In this article, we'll cover:

What is TNSNAMES.ORA?
Where is the TNSNAMES.ORA file?
What order does Oracle check these locations
TNSNAMES Location on Unix
TNSNAMES Location on Windows
What Is The Syntax?
Modifying a TNSNAMES.ORA file
Creating a TNSNAMES.ORA file
TNSNAMES Example
SQL Developer and TNSNAMES.ORA

What Is TNSNAMES.ORA?

TNSNAMES.ORA is a configuration file that the Oracle database uses. It allows users and applications to connect to Oracle databases by matching a connection name with all of the relevant details.

It’s written using a specific syntax, which I’ll cover later in this article. The good news is that it can be edited with any text editor.

The file and this article refer to a few different terms, such as service names and connect descriptors, which I’ll cover later in this article.

While you're here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

Where Is the TNSNAMES.ORA File Located?

The location of the TNSNAMES.ORA file is:

1$ORACLE_HOME\network\admin\

What does this mean?

Well, $ORACLE_HOME is an environment variable. It works in the same way as a variable in a programming language, but it’s sits in your operating system.

In this case, $ORACLE_HOME is the location that the Oracle database is installed in. This environment variable, or path, works the same on Unix and Windows operating systems.

So, how do you find your $ORACLE_HOME value? I’ll show you how to do that in the next section.

There are some other locations that the TNSNAMES.ORA file can be stored in:

Client Machine

The ORACLE_HOME\network\admin folder on your client machine. There is a file on both the server and the client.

TNS_ADMIN Environment Variable

There is another environment variable called TNS_ADMIN. The location of this folder could also have a TNSNAMES file.

To find the location of TNS_ADMIN, follow the same steps below to find ORACLE_HOME, but substitute the TNS_ADMIN value.

What Order Does Oracle Check These Locations In?

Because there are several locations for the TNSNAMES.ORA file, they are checked in a certain order:

If TNS_ADMIN is set, then this location is checked first. If the file is not found in this directory, it is assumed the file does not exist. You may need to create one.
In Windows, if the TNS_ADMIN environment variable is not set, then the registry is checked for the TNS_ADMIN parameter and checks that directory.
If the TNS_ADMIN variable is not set, then the ORACLE_HOME\network\admin directory is checked.

How To Find ORACLE_HOME and the TNSNAMES.ORA Location in Unix

To find the location of ORACLE_HOME in Unix, you can run these commands:

1env | grep ORACLE_HOME

Or, you can run the echo command

1echo $ORACLE_HOME

How To Find ORACLE_HOME and the TNSNAMES.ORA Location in Windows

To find the ORACLE_HOME location in Windows, we can check a few places.

First, we'll check the Environment Variables in the control panel. If it's not there, we'll check the registry.

To start, open the Control Panel.

Then, open System.

Click on Advanced System Settings, on the left.

Click on the Advanced tab (if it is not already selected) and click Environment Variables down the bottom.

Check the User Variables section and the System Variables section for a variable called ORACLE_HOME. If it is shown, then the Value will be your ORACLE_HOME location.

If it does not exist, it means you'll need to check the registry. It isn't showing in my Environment Variables, so I'll check the registry.

Open the Run command box (on older versions of Windows), or if you're on Windows 10, just open the Start menu.

Type regedit and press Enter.

On the left panel, navigate to this location by expanding the folders:

1HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

Now, you'll need to click on the item below Oracle on the left. This may be called KEY_XE (if you're running Oracle Express like I am) or KEY_OraDb11g or something similar.

There will be an entry in this list on the left called ORACLE_HOME.

This is your ORACLE_HOME location. For example:

1C:\oraclexe\app\oracle\product\11.2.0\server

To navigate to it, double-click on the line labelled ORACLE_HOME.

Copy the Value here, and paste it into Windows Explorer.

What Is The Syntax of the TNSNAMES.ORA File?

This file contains a series of entries, where each of them represents a connection string to the database.

An entry will look like this:

1net_service_name =
2  (DESCRIPTION=
3    (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.com)(PORT = 1521)
4  )
5  (CONNECT_DATA =
6    (SERVICE_NAME=service_name)
7  )
8)

What does this mean?

net_service_name: This is the name that you use for a connection string later. You can choose what this is. It’s like a name you give to this set of connection details.
host: The IP address or server name where the database lives or that you want to connect to.
port: The port that is required for the connection. In most cases the default port of 1521 will be fine.
service_name: This is the name of the database you want to connect to.

What about the SID? The SID parameter was used in older versions of Oracle in this file (Oracle 8 and earlier). The service_name parameter should be used instead.

How Can I Modify the TNSNAMES.ORA File?

You can modify the file in a simple text editor. You can change an existing entry or create a new one.

To add an entry into the file, you can either copy the format from above, or copy and paste an existing entry from the file.

Then, make changes to it as needed.

Change the net_service_name, or the name you want to give to the connection. Change the host to the server name or IP address you want to connect to. Finally, change the service_name to the name of the database you want to connect to.

Save the file, and your changes will be saved.

How Can I Create a TNSNAMES.ORA File?

If you don’t have a TNSNAMES.ORA file in your ORACLE_HOME directory, you can create one. Or you can create one for any other reason.

To create the file, open a new text file in the editor of your choice (I use Notepad++).

Save the file with the name TNSNAMES.ORA (not a .txt file) and save it into your ORACLE_HOME location.

Now, add in a template for the entry you want to create:

1net_service_name =
2  (DESCRIPTION=
3    (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.com)(PORT = 1521)
4  )
5  (CONNECT_DATA =
6    (SERVICE_NAME=service_name)
7  )
8)

Then, change the parameters to what you need to store for your database connection:

net_service_name: the name you give to this connection, which will be used when you connect to it later.
host: the server or IP address that the database runs on
service_name: the name of the database you’re connecting to.

See below for an example of this.

TNSNAMES.ORA Entry Example

Here’s an example of an entry in this file:

1ora_test =
2  (DESCRIPTION=
3    (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver.yourcompany.com)(PORT = 1521)
4  )
5  (CONNECT_DATA =
6    (SERVICE_NAME=oratst)
7  )
8)

This means that the database runs on the server that’s called “oracle server.yourcompany.com”. The database name is orates, and when you connect to it, you’ll refer to this as ora_test.

Does SQL Developer Use TNSNAMES.ORA?

Yes, it does. In SQL Developer, you can set the location of your TNSNAMES.ORA file, which will give you additional options when creating connections to a database.

In SQL Developer, open Tools > Preferences.

Expand the Database section and click on Advanced.

SQL Developer Preferences - Database Advanced for TNSNAMES

In the Tnsnames Directory option at the bottom of the screen, add the location of the TNSNAMES.ORA file. This will be ORACLE_HOME\network\admin as mentioned earlier.

Then, click OK.

Now, when you create a new connection, you can use this TNSNAMES data.

Click Create New Connection (the green + sign on the top left of SQL Developer).

In the Connection Type drop-down, select TNS.

Selecting TNS will allow you to select your connection details from the TNSNAMES file. This makes it easier to manage.

Jeff Smith has written more about how SQL Developer finds these files in this article.

Conclusion

The TNSNAMES.ORA file is used by Oracle to store and configure the connection details to different databases. It can be hard to find, but using this guide will make it easier. Making changes is easy, as it’s a simple text file with a specific format. It might not be something a database developer would need to use that often, but it's still good to know.

While you're here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here: