Oracle TNS names not showing when adding new connection to sqldeveloper

Question

Hello,

I'm trying to connect to an oracle database with sqldeveloper.

I've installed the .Net oracle drivers and placed the tnsnames.ora file at C:\Oracle\product\11.1.0\client_1\Network\Admin.

I'm using the following format in tnsnames.ora.

dev =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.XXX.XXX)(PORT = XXXX))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = idpdev2)
)
)

In sqldeveloper, when I try to create a new connection, no tns names show up as options.

Is there something I'm missing?

Answer 1

SQL Developer will look in the following location in this order for a tnsnames.ora file

1. $HOME/.tnsnames.ora
2. $TNS_ADMIN/tnsnames.ora
3. TNS_NAMES lookup key in the registry
4. /etc/tnsnames.ora ( non-windows )
5. $ORACLE_HOME/network/admin/tnsnames.ora
6. LocalMachine\SOFTWARE\ORACLE\ORACLE_HOME_KEY
7. LocalMachine\SOFTWARE\ORACLE\ORACLE_HOME

If your tnsnames.ora file is not getting recognized, use the following procedure:

1. Define an environmental variable called TNS_ADMIN to point to the folder that contains your tnsnames.ora file.

   In Windows, this is done by navigating to Control Panel > System > Advanced Tab > Environment Variables

   In Linux, define the TNS_ADMIN variable in the .profile file in your home directory.

2. Confirm the os is recognizing this environmental variable and restart SQL Developer. From the windows command line:
   echo %TNS_ADMIN%

   From linux: echo $TNS_ADMIN

3. Now in SQL Developer right click on Connections and select new connection. Select connection type TNS in the drop down box. Your entries from tnsnames.ora should now display here.

Answer 2

You can always find out the location of the tnsnames.ora file being used by running TNSPING to check connectivity (9i or later):

C:>tnsping dev

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 08-JAN-2009 12:48:38

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files: C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\sqlnet.ora

Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = XXX)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = DEV))) OK (30 msec)

C:>

Sometimes, the problem is with the entry you made in tnsnames.ora, not that the system can't find it. That said, I agree that having a tns_admin environment variable set is a Good Thing, since it avoids the inevitable issues that arise with determining exactly which tnsnames file is being used in systems with multiple oracle homes.

Answer 3

Hi,

I tried the above mentioned Steps by Jason but i am still not able to get the TNS. Help Please

Answer 4

The steps mentioned by Jason are very good and should work. There is a little twist with SQL Developer, though. It caches the connection specifications (host, service name, port) the first time it reads the tnsnames.ora file. Then, it does not invalidate the specs when the original entry is removed from the tnsname.ora file. The cache persists even after SQL Developer has been terminated and restarted. This is not such an illogical way of handling the situation. Even if a tnsnames.ora file is temporarily unavailable, SQL Developer can still make the connection as long as the original specifications are still true. The problem comes with their next little twist. SQL Developer treats service names in the tnsnames.ora file as case-sensitive values when resolving the connection. So if you used to have an entry name ABCD.world in the file and you replaced it with an new entry named abcd.world, SQL Developer would NOT update its connection specs for ABCD.world - it will treat abcd.world as a different connection altogether. Why am I not surprised that an Oracle product would treat as case-sensitive the contents of an oracle-developed file format that is expressly case-insensitive?