Oracle TNS names not showing when adding new connection to SQL Developer

Question

I m trying to connect to an oracle database with SQL Developer    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 SQL Developer  when I try to create a new connection  no TNS-names show up as options   Is there something I m missing

User · Accepted Answer

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

$HOME/.tnsnames.ora
$TNS_ADMIN/tnsnames.ora
TNS_ADMIN lookup key in the registry
/etc/tnsnames.ora ( non-windows )
$ORACLE_HOME/network/admin/tnsnames.ora
LocalMachine\SOFTWARE\ORACLE\ORACLE_HOME_KEY
LocalMachine\SOFTWARE\ORACLE\ORACLE_HOME

To see which one SQL Developer is using, issue the command show tns in the worksheet

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

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 system settings > Environment Variables...

In Linux, define the TNS_ADMIN variable in the .profile file in your home directory.
Confirm the os is recognizing this environmental variable

From the Windows command line: echo %TNS_ADMIN%

From linux: echo $TNS_ADMIN
Restart SQL Developer
Now in SQL Developer right click on Connections and select New Connection.... Select TNS as connection type in the drop down box. Your entries from tnsnames.ora should now display here.

User · Answer

In Sql Developer  navidate to Tools- preferences- Datababae- advanced- Set Tnsname directory to the directory containing tnsnames ora

User · Answer

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

User · Answer

Open SQL Developer  Go to Tools -  Preferences -  Databases -  Advanced Then explicitly set the Tnsnames Directory  My TNSNAMES was set up correctly and I could connect to Toad  SQL Plus etc  but I needed to do this to get SQL Developer to work   Perhaps it was a Win 7 issue as it was a pain to install too

User · Answer

You can always find out the location of the tnsnames ora file being used by running TNSPING to check connectivity  9i or later    C   gt 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   gt    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

User · Answer

You can always find out the location of the tnsnames ora file being used by running TNSPING to check connectivity  9i or later    C   gt 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   gt    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

User · Answer

None of the above changes made any difference in my case  I could run TNS PING  in the command window but SQL Developer couldn t figure out where tnsnames ora was   The issue in my case  Windows 7 - 64 bit - Enterprise   was that the Oracle installer pointed the Start menu shortcut to the wrong version of SQL Developer  There appear to be three SQL Developer instances that accompany the installer  One is in  ORACLE HOME  client 1 sqldeveloper  and two are in  ORACLE HOME  client 1 sqldeveloper bin      The installer installed a start menu shortcut that pointed at a version in the bin directory that simply did not function  It would ask for a password every time I started SQL Developer  not remember choices I had made and displayed a blank list when I chose TNS as the connection mechanism  It also does not have the TNS Directory field in the Database advanced settings referenced in other posts    I tossed the old Start shortcut and installed a shortcut to   ORACLE HOME  client 1 sqldeveloper sqldeveloper exe   That change fixed the problem in my case

User · Answer

Open SQL Developer  Go to Tools -  Preferences -  Databases -  Advanced Then explicitly set the Tnsnames Directory  My TNSNAMES was set up correctly and I could connect to Toad  SQL Plus etc  but I needed to do this to get SQL Developer to work   Perhaps it was a Win 7 issue as it was a pain to install too

User · Answer

You can always find out the location of the tnsnames ora file being used by running TNSPING to check connectivity  9i or later    C   gt 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   gt    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

User · Answer

In SQLDeveloper browse Tools -- gt  Preferences  as shown in below image   In the Preferences options expand Database -- gt  select Advanced -- gt  under  Tnsnames Directory  -- gt  Browse the directory where tnsnames ora present Then click on Ok  as shown in below diagram        You have Done    Now you can connect via the TNSnames options

User · Answer

None of the above changes made any difference in my case  I could run TNS PING  in the command window but SQL Developer couldn t figure out where tnsnames ora was   The issue in my case  Windows 7 - 64 bit - Enterprise   was that the Oracle installer pointed the Start menu shortcut to the wrong version of SQL Developer  There appear to be three SQL Developer instances that accompany the installer  One is in  ORACLE HOME  client 1 sqldeveloper  and two are in  ORACLE HOME  client 1 sqldeveloper bin      The installer installed a start menu shortcut that pointed at a version in the bin directory that simply did not function  It would ask for a password every time I started SQL Developer  not remember choices I had made and displayed a blank list when I chose TNS as the connection mechanism  It also does not have the TNS Directory field in the Database advanced settings referenced in other posts    I tossed the old Start shortcut and installed a shortcut to   ORACLE HOME  client 1 sqldeveloper sqldeveloper exe   That change fixed the problem in my case

User · Answer

You can always find out the location of the tnsnames ora file being used by running TNSPING to check connectivity  9i or later    C   gt 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   gt    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

User · Answer

In Sql Developer  navidate to Tools- preferences- Datababae- advanced- Set Tnsname directory to the directory containing tnsnames ora

User · Answer

In SQLDeveloper browse Tools -- gt  Preferences  as shown in below image   In the Preferences options expand Database -- gt  select Advanced -- gt  under  Tnsnames Directory  -- gt  Browse the directory where tnsnames ora present Then click on Ok  as shown in below diagram        You have Done    Now you can connect via the TNSnames options

User · Answer

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

[database] Oracle TNS names not showing when adding new connection to SQL Developer

Examples related to database

Examples related to oracle

Examples related to oracle-sqldeveloper

Examples related to tnsnames

Examples related to tns