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:
$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:
env | grep ORACLE_HOME
Or, you can run the echo command
echo $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:
HKEY_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:
C:\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:
net_service_name =
(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.com)(PORT = 1521)
)
(CONNECT_DATA =
(SERVICE_NAME=service_name)
)
)
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:
net_service_name =
(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.com)(PORT = 1521)
)
(CONNECT_DATA =
(SERVICE_NAME=service_name)
)
)
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:
ora_test =
(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver.yourcompany.com)(PORT = 1521)
)
(CONNECT_DATA =
(SERVICE_NAME=oratst)
)
)
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.
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
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:
Thank you sir!
No problem, glad you like it!
Excellent
I have issues with TNSname when testing connection:
message box
Unable to connect
SQLstate=08004
[oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specified.
Please advise
Hi Leo, is your database running? Is the name of the database the same as the one used in the connection box?
2 related questions re: the tnsnames.ora – the first, how does Oracle handle 2 tnsnames entries that use the same alias (assume this was done in error)? The second question, how does Oracle search through the tnsnames file, from the top down or from the bottom up (again, this is related to duplicate entries using the same alias)?
Thanks.
Hi JS,
To be honest I’m not sure about either of those. I haven’t had that situation happen. I could test it to see what happens!
I would suggest asking either StackOverflow or AskTOM. There are a lot of Oracle experts on AskTOM who probably know how the TNSNAMES works in those situations.
In windows you can find tnsnames.ora using “DIR /A/S tnsnames.ora” at command prompt when you can’t find it in regular places.
Thank you. I have one query, i need to take tnsnames.ora file from local path instead of TNS_ADMIN environment variable. How can i do that. I need to do this as i am getting access denied message when i take tnsname.ora file from TNS_ADMIn environment path.
How to modify tnsnames.ora file without admin account?
Someone will need to grant the Authenticated Users (Windows) read/write access to the network/admin folder. Then you need to grant read/write or full control to the user(s) authorized to change tnsnames.ora.
Fantastic article that explains the function and syntax of the tnsnames.ora far better than the official Oracle documentation.
Glad you found it useful! I agree the Oracle docs can sometimes be hard to understand.
Nicely captured the basics. Thank you!
hi ben i just have some issue whenever i am trying to connect through OCI_CONNECT in php to my oracle database it says “ORA-03134: Connections to this server version are no longer supported.”
I asked someone who executed that said u use DNSNAMES.ORA it will work. But i dont understand how to use it all . Pls help
This is very helpful, thank you.
If oracle entry does not exist in the registry too, than what should we have to do.
No entries found in tns what should I do?
Thank You so much!!
That was very helpful. Thank you.
Hi, can I force the database schema’s name in the tnsnames.ora file?
Context :
I access a development database schema located on the same service as others and I have to indicate for each query the used schema.
My production schema has another name and I’d like to find a solution to indicate the default schema at the connection level in order not to have to change all my queries at the deployment step.
–> I have seen that I can add the RDB_DATABASE parameter in the CONNECT_DATA section of the tnsnames file but I can’t find the syntax to use. Could you give me examples?
Thanks
I have just added a new entry to my tnsnames.ora file. I can connect to the database using SQL Developer no problem. However I get an error (ORA-12154: TNS: could not resolve the connect identifier specified) when trying to connect with the ODBC Adminstrator – unless I take out all the other entries, and then it works fine. Any ideas as to what the problem is?
Very Nice :) Thank you!
Thanks