How To Set Tnsnames Ora In Sql Developer

The TNSNAMES.ORA file is an of import file when working with Oracle. Learn all about what information technology 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 Case
• 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 past matching a connectedness name with all of the relevant details.

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

The file and this commodity refer to a few different terms, such as service names and connect descriptors, which I'll cover later 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 hateful?

Well, $ORACLE_HOME is an environs variable. Information technology works in the same way as a variable in a programming language, but information technology'southward sits in your operating organisation.

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

Then, how do you notice 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 exist 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

At that place is another surround variable called TNS_ADMIN. The location of this folder could as well have a TNSNAMES file.

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

What Order Does Oracle Check These Locations In?

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

1. If TNS_ADMIN is ready, then this location is checked first. If the file is not institute in this directory, it is assumed the file does not exist. You may need to create one.
2. In Windows, if the TNS_ADMIN environs variable is not gear up, then the registry is checked for the TNS_ADMIN parameter and checks that directory.
3. If the TNS_ADMIN variable is not ready, 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 control

repeat $ORACLE_HOME

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

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

First, we'll check the Environment Variables in the control panel. If information technology's not at that place, we'll cheque the registry.

To start, open the Control Panel.

And so, open System.

Click on Avant-garde Organization Settings, on the left.

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

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

If information technology does non exist, it means you'll need to bank 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 Get-go 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 y'all're running Oracle Limited like I am) or KEY_OraDb11g or something similar.

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

This is your ORACLE_HOME location. For instance:

C:\oraclexe\app\oracle\production\11.ii.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 volition await 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 yous use for a connexion string after. You can choose what this is. It's like a proper name you requite to this set up of connection details.
• host: The IP address or server proper name where the database lives or that you desire to connect to.
• port: The port that is required for the connection. In most cases the default port of 1521 volition exist fine.
• service_name: This is the proper noun of the database you want to connect to.

What nearly 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 tin alter the file in a uncomplicated text editor. You lot can change an existing entry or create a new one.

To add an entry into the file, you can either copy the format from in a higher place, or copy and paste an existing entry from the file.

Then, make changes to it as needed.

Modify 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 lot want to connect to. Finally, change the service_name to the proper 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 take a TNSNAMES.ORA file in your ORACLE_HOME directory, y'all tin create one. Or yous can create one for any other reason.

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

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

At present, add in a template for the entry you lot desire 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 proper noun yous give to this connection, which will be used when you connect to it afterwards.
• host: the server or IP address that the database runs on
• service_name: the proper name of the database you're connecting to.

See below for an case of this.

TNSNAMES.ORA Entry Example

Here'due south an example of an entry in this file:

ora_test =   (Description=     (Accost = (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 proper name is orates, and when you connect to information technology, you'll refer to this as ora_test.

Does SQL Developer Utilize TNSNAMES.ORA?

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

In SQL Programmer, open Tools > Preferences.

Expand the Database section and click on Advanced.

In the Tnsnames Directory pick at the lesser of the screen, add in 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 connectedness, y'all can use this TNSNAMES data.

Click Create New Connection (the greenish + sign on the superlative left of SQL Developer).

In the Connection Type driblet-down, select TNS.

Selecting TNS will allow you to select your connexion 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 commodity.

Conclusion

The TNSNAMES.ORA file is used by Oracle to store and configure the connexion details to different databases. It can be hard to find, simply using this guide will make it easier. Making changes is easy, as it'south a simple text file with a specific format. It might not exist something a database developer would demand to use that frequently, but it's even so practiced to know.

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

Source: https://www.databasestar.com/tnsnames-ora-file/

Share this post