How To Check If Oracle Locator Is Installed

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

In this commodity, 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 Programmer 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 past matching a connection name with all of the relevant details.

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

The file and this article refer to a few dissimilar terms, such every bit service names and connect descriptors, which I'll cover subsequently in this article.

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 surround variable. It works in the same mode as a variable in a programming language, but it's sits in your operating arrangement.

In this instance, $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 y'all find your $ORACLE_HOME value? I'll bear witness you how to do that in the next section.

There are some other locations that the TNSNAMES.ORA file tin 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 customer.

TNS_ADMIN Environment Variable

At that place is another environment variable chosen TNS_ADMIN. The location of this folder could likewise accept a TNSNAMES file.

To find the location of TNS_ADMIN, follow the aforementioned steps beneath to find ORACLE_HOME, but substitute the TNS_ADMIN value.

What Society Does Oracle Bank 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 gear up, and so this location is checked start. If the file is non found in this directory, it is assumed the file does not exist. You may demand to create i.
In Windows, if the TNS_ADMIN environment variable is not set, and so the registry is checked for the TNS_ADMIN parameter and checks that directory.
If the TNS_ADMIN variable is non prepare, then the ORACLE_HOME\network\admin directory is checked.

How To Observe 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, yous 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 at that place, we'll check the registry.

To beginning, open the Command Console.

Then, open Arrangement.

Click on Advanced System Settings, on the left.

Click on the Advanced tab (if information technology is non already selected) and click Environs Variables downwardly 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, information technology ways you'll need to check the registry. It isn't showing in my Surround Variables, so I'll cheque the registry.

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

Type regedit and printing Enter.

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

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

Now, y'all'll demand to click on the item below Oracle on the left. This may be chosen KEY_XE (if y'all're running Oracle Limited like I am) or KEY_OraDb11g or something like.

At that place will be an entry in this list on the left called ORACLE_HOME.

This is your ORACLE_HOME location. For instance:

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

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

Copy the Value hither, 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 volition look like this:

net_service_name =   (DESCRIPTION=     (ADDRESS = (PROTOCOL = TCP)(HOST = thirty.xxx.com)(PORT = 1521)   )   (CONNECT_DATA =     (SERVICE_NAME=service_name)   ) )

What does this mean?

net_service_name: This is the proper noun that you use for a connection string later on. Yous can cull what this is. It's similar a proper name you requite to this fix of connection details.
host: The IP address or server proper noun 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 volition be fine.
service_name: This is the proper name of the database you want to connect to.

What well-nigh 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 Change the TNSNAMES.ORA File?

You can modify the file in a simple text editor. Yous can alter an existing entry or create a new i.

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

Then, make changes to it as needed.

Alter the net_service_name, or the proper noun you want to give to the connection. Change the host to the server proper name or IP address yous want to connect to. Finally, modify the service_name to the name of the database y'all want to connect to.

Salve the file, and your changes volition be saved.

How Can I Create a TNSNAMES.ORA File?

If you don't accept a TNSNAMES.ORA file in your ORACLE_HOME directory, y'all can create i. Or you can create one for any other reason.

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

Save the file with the name TNSNAMES.ORA (non a .txt file) and save information technology into your ORACLE_HOME location.

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

net_service_name =   (DESCRIPTION=     (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.com)(PORT = 1521)   )   (CONNECT_DATA =     (SERVICE_NAME=service_name)   ) )

And then, alter the parameters to what you lot demand to store for your database connectedness:

net_service_name: the proper noun you requite to this connexion, which volition exist used when you connect to it later.
host: the server or IP accost 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 Instance

Hither'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 Programmer Use TNSNAMES.ORA?

Aye, it does. In SQL Programmer, you tin can prepare 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 choice at the bottom of the screen, add together in the location of the TNSNAMES.ORA file. This will exist ORACLE_HOME\network\admin every bit mentioned earlier.

Then, click OK.

At present, when you create a new connection, yous tin can apply this TNSNAMES data.

Click Create New Connection (the green + sign on the meridian left of SQL Programmer).

In the Connection Type drop-downwardly, select TNS.

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

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

Decision

The TNSNAMES.ORA file is used by Oracle to shop and configure the connection details to different databases. It can be hard to detect, but using this guide volition make it easier. Making changes is easy, as it's a unproblematic text file with a specific format. Information technology might not be something a database developer would need to use that oftentimes, just information technology's nevertheless practiced to know.

Lastly, if you bask the information and career advice I've been providing, sign up to my newsletter below to stay up-to-appointment on my articles. You'll also receive a fantastic bonus. Thanks!