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

Asked 8/1, 2009 at 16:57 Answered 23/5, 2022 at 8:47

Solved database oracle oracle-sqldeveloper tnsnames tns

102

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?

Strobe answered 8/1, 2009 at 16:57 Comment(0)

183

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.

Sapling answered 8/1, 2009 at 17:17 Comment(7)

For me: $HOME/.tnsnames.ora didn't work ... I used $HOME/tnsnames.ora instead. Also Note: You will need to go to Tools -> Preferences, search for "tns" and point the directory to $HOME – Easel 14/12, 2012 at 21:30

Despite this being in release notes for SQL developer 1.5 version, I found that on Ubuntu 12.04 LTS only #5 worked. Try these steps to check if that's your case: forums.oracle.com/message/2769285#2769285. Noteworthy: author had the problem on Win Vista. – Podgy 6/8, 2013 at 8:21

Worked for me in Windows. Logging things forever FTW! – Hearten 5/11, 2013 at 21:37

Setting TNS_ADMIN did not work for me. Instead, I searched the computer for all TNSNAMES Files and updated the contents of all of them. No idea why Windows 7 Sql Developer persisted on using some other TNSNAMES File in some old folder. – Cerussite 17/7, 2014 at 8:11

Note: $TNS_ADMIN is the DIRECTORY not the actual .ora file – Ferret 18/9, 2018 at 14:53

I found that the env var for TNS_ADMIN needed a path that ended in a slash. Without the trailing slash SQL Developer could not see the tnsnames.ora file. – Prelatism 31/5, 2019 at 17:32

The ENV Variable for windows worked for me but i am curious to know (from windows machine perspective), $HOME or $ORACLE_HOME isn't the same as adding them as environment vars? Is there any ideal way possible without adding the Env variable explicitly? Or did i miss something in the Installation of the Instantclient setup ? I went with custom installation and chose ODBC, ODP.net only. – Asymptote 9/3, 2023 at 17:30

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.

Gail answered 9/8, 2011 at 23:18 Comment(2)

Accepted answer did not work for me but this did (Windows Server 2008R2) – Barina 9/6, 2015 at 18:53

Had the same issue, but SQL Developer also looked in %userprofile%\tnsnames.ora before %TNS_ADMIN%\tnsnames.ora, and that's where I had the extra tnsnames.ora file. – Dulin 30/6, 2020 at 14:39

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.

Denotative answered 8/1, 2009 at 19:56 Comment(2)

how can I install tnsping tool ?? – Rae 10/1, 2011 at 11:11

In the client install there is a checkbox for Database Tools or some such. – Denotative 20/5, 2011 at 17:5

In SQLDeveloper browse Tools --> Preferences, as shown in below image.

enter image description here

In the Preferences options expand Database --> select Advanced --> under "Tnsnames Directory" --> Browse the directory where tnsnames.ora present.
Then click on Ok.
as shown in below diagram.

enter image description here

You have Done!

Now you can connect via the TNSnames options.

Dovekie answered 23/7, 2012 at 12:33 Comment(3)

Can't find "Databse:Advanced" ==>my version is 1.5.5 – Booted 28/1, 2013 at 20:21

For versions lower than 2.1 or if you haven't specified a Tnsnames directory on 2.1 or higher please fallow as specified in @JasonAnderson answer. – Dovekie 29/1, 2013 at 5:25

I do not have Tnsnames Directory option in Tools->Preferences->Databaes->Advanced Parameters menu. I'm using SqlDeveloper v1.1.3 – Pragmatic 24/9, 2013 at 7:31

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?

Stile answered 8/1, 2009 at 16:57 Comment(1)

@allen: do you know how to force it to clear this list? If so, please respond to https://mcmap.net/q/162011/-sqldeveloper-clear-network-alias-list/168646 – Creech 20/6, 2011 at 14:14

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

Loos answered 22/5, 2012 at 14:16 Comment(0)

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.

Dragelin answered 24/3, 2014 at 19:42 Comment(0)

At windows I must add permision "READ & execure" for:

folder c:\oracle\TNS_ADMIN
file c:\oracle\TNS_ADMIN\tnsnames.ora

of course the Windows see:

C:\>set TNS
TNS_ADMIN=c:\oracle\TNS_ADMIN

and now SQL Developer see TNS on list.

Pelt answered 23/5, 2022 at 8:47 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags