How to Use TNSNAMES.ORA in Oracle SQL Developer and PL/SQL Developer

6.4K

The TNSNAMES.ORA is a configuration file that defines Oracle databases’ addresses and connection settings. Each entry in this file provides connection details, including the net service name, host address (IP or FQDN), protocol (usually TCP), port, and Oracle service name or SID. Oracle clients use the TNSNAMES.ORA file to connect to Oracle databases by referring to these aliases instead of specifying full connection details each time.

What is the TNSNAMES.ORA File in Oracle DB?

Oracle clients use Oracle Net Services to resolve TNS aliases defined in the tnsnames.ora file into Oracle database connection descriptors. It is a plain text file that can be edited with any text editor. Here’s an example of a typical entry in the TNSNAMES.ORA file that describes an Oracle service alias:

PROD_ERP =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = db01.example.com)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVICE_NAME = erp_prod.example.com)
)
)

What does this mean?

• net_service_name (de_mun_sap12) – this is the network service alias that the client uses to identify the connection descriptor.
• host (db01.example.com) – the hostname or IP address of the server hosting the Oracle Net listener.
• port (1521) – connection port (default is 1521).
• service_name (erp_prod.example.com) – the Oracle Database service to which the client connects. A service is a logical entity that can be associated with a CDB, PDB, or one or more database instances. Its name does not have to match the database SID or instance name. In RAC environments, you can configure a service to run on multiple instances for high availability and load balancing. In multitenant environments, a service can be associated with a specific PDB, allowing clients to connect directly to that PDB.

The tnsnames.ora file is located by default in the following location on both the client and the server:

• Windows — %ORACLE_HOME%\network\admin
• Linux — $ORACLE_HOME/network/admin

Where ORACLE_HOME is the directory that contains your primary Oracle installation (for example, C:\APP\db_home).

Hints

• The values in the tnsnames.ora file are not case sensitive. However, they must match the service name exactly as it is registered with the listener.
• If the connectivity information for the Oracle database changes, the tnsnames.ora file on the client computer will need to be updated manually.
• If you prefer a GUI, you can use the Oracle Net Configuration Assistant (NETCA) to create or modify Oracle Net config files such as tnsnames.ora.

Find Oracle folder location

To find the Oracle folder location in the ORACLE_HOME environment variable, run:

• Linux: env | grep ORACLE_HOME
• Windows: echo %ORACLE_HOME%

Additionally, Oracle Net also checks other locations for the TNSNames.ora file:

• The directory specified by the TNS_ADMIN environment variable (this can be local session or global environment variable).
  

  

• User registry setting under HKCU\Software\Oracle or system registry hive HKLM\Software\Oracle (check the path in the ORACLE_HOME registry item).
• Application’s current directory.
  

  

On Windows, the TNS_ADMIN environment variable is used to point to a specific TNSNAMES.ORA file when multiple Oracle client versions are installed.

The problem is that most database hosts don’t have a TNS_ADMIN environment variable set by default. However, you can use the ORACLE_HOME variable to point to it.

set TNS_ADMIN=%ORACLE_HOME%\network\admin

Importing TNSNAMES.ORA to SQL Developer

Oracle SQL Developer is Oracle’s free integrated development environment (IDE) for working with Oracle databases using SQL and PL/SQL. Modern versions can work either with the built-in JDBC Thin driver or with an installed Oracle Client (OCI/Thick mode). TNS aliases are typically loaded from Oracle Net config files when Oracle Client integration is enabled/when the TNS_ADMIN folder is configured manually.

Depending on the Oracle Client and SQL Developer config, SQL Developer may check the following locations:

• %TNS_ADMIN%\tnsnames.ora
• %ORACLE_HOME%\network\admin\tnsnames.ora
• Instant Client network/admin folder

In case you need to manually config the TNS path in SQL Developer:

• Open Oracle SQL Developer;
• Go to Tools > Preferences > Database > Advanced;
• Specify the directory containing the tnsnames.ora file in the Tnsnames Directory field;
• Restart SQL Developer;
• Create a new connection and select Connection Type = TNS.

After restart, all TNS aliases from the tnsnames.ora file should appear in the connection list.

If SQL Developer doesn’t list the available Oracle database (DB) connections, it means that it did not find the tnsnames.ora file in the default locations.

In this case, you can either specify the path manually in the TNS_ADMIN variable or specify the path to the tnsnames.ora file in SQL Developer. The examples below show how to add persistent environment variables in Linux and Windows.

• Windows (PowerShell):
  

  $oracleHome = $env:ORACLE_HOME
  
  if (-not $oracleHome) {
  throw "ORACLE_HOME is not defined in the current PowerShell session."
  }
  
  $tnsAdmin = Join-Path $oracleHome 'network\admin'
  
  [Environment]::SetEnvironmentVariable(
  'TNS_ADMIN',
  $tnsAdmin,
  'Machine'
  )

• Linux (add to /etc/profile): export TNS_ADMIN=/path/to/oracle/network/admin

Using TNSNAMES.ORA in PL/SQL Developer

PL/SQL Developer by Allround Automations is a separate third-party Oracle development tool that also supports Oracle Net connections through the tnsnames.ora file. It searches for the Oracle Home and TNS config using:

• TNS_ADMIN environment variable
• Oracle client registry settings
• Oracle Home network/admin directory

If you need to config the TNS file location manually in PL/SQL Developer:

• Open PL/SQL Developer;
• Go to Tools > Preferences > Oracle > Connection;
• Specify the OCI Library and Oracle Home;
• Make sure that the Oracle client points to the correct tnsnames.ora location;
• Restart the app.

You can verify the detected Oracle Home and TNS config using: Help > Support Info.

Using TNSNAMES.ORA in SQL*Plus

SQL*Plus uses Oracle Net Services and the tnsnames.ora file to resolve Oracle database aliases in the same way as other Oracle client tools.

In order to connect to a database using a TNS alias, use a command:

sqlplus username/password@PROD_ERP

For security reasons, many admins prefer not to specify passwords directly in the command line. Instead, use the commands:

sqlplus /nolog
connect username@de_mun_sap12

In case SQL*Plus cannot resolve the alias, you need to verify:

• That the Oracle Client is installed correctly
• The TNS_ADMIN variable points to the correct folder
• The tnsnames.ora file contains the required alias
• The Oracle listener is reachable

You can verify the active Oracle environment:

On Windows machine:

echo %ORACLE_HOME%
echo %TNS_ADMIN%

On Linux:

env | grep ORACLE

Test Oracle Database Connectivity with TNSPING

If the specified Oracle instances are not available in SQL Developer, verify connectivity to them using the tnsping tool. The tnsping command uses information from the tnsnames.ora file to check connectivity with the Oracle database host.

For example, use the following command to check the connectivity of the DB alias for the entry in the tnsnames.ora file:

tnsping PROD_ERP

The following lines should appear:

Used TNSNAMES adapter to resolve the alias

Attempting to contact ….

If the connection is successful, OK will appear. If incorrect Net Service Names are specified in the tnsname file, you will get an error:

TNS-03505: Failed to resolve name

If the file contains a valid server address but does not have a listener running on it, the tnsping command will return an error:

TNS-12541: TNS:no listener

EZCONNECT syntax

Easy Connect is a long-established Oracle Net naming method. It is particularly convenient for cloud, containerized, automation, and ephemeral environments where maintaining a local tnsnames.ora file is undesirable.

For example, you can use the command:

sqlplus username/password@//dbserver01:1521/orclpdb

EZCONNECT is commonly used in containerized environments, cloud deployments, automation scripts, and temporary admin connections.