ChatGPT解决这个技术问题 Extra ChatGPT

Use tnsnames.ora in Oracle SQL Developer

I am evaluating Oracle SQL Developer.

My tnsnames.ora is populated, and a tnsping to a connection defined in tnsnames.ora works fine. Still, SQL Developer does not display any connections.

Oracle SQL Developer Soars mentions, that if

you have Oracle client software and a tnsnames.ora file already installed on your machine, Oracle SQL Developer will automatically populate the Connections navigator from the net service names defined in tnsnames.ora.

I also tried to set my TNS_ADMIN environment variable, but after restarting SQL Developer there are still no connections displayed.

Any ideas? Anyone successfully working with SQL Developer and tnsnames.ora?

I downloaded and installed the Oracle SQL Developer to connect to an already existing Oracle 11g database. But it seems that I will need to download and install Oracle client software also, which one do I use if I am on Windows 10?

C
Chandra Sekhar

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

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. tnsnames.ora available at Drive:\oracle\product\10x.x.x\client_x\NETWORK\ADMIN

Now you can connect via the TNSnames options.


everyone, Just a little update on this old (but quite helpfull) post : don't try to use a symbolic link on Windows. SQL developper doesn't seem to know how to use it. Made me lose some time. Hope it'll save yours.
@Hellday5432 - Yes that is correct. SQL Dev runs on Java which doesn't understand shortcuts; for example when opening a file we cannot use a shortcut to jump to a directory.
Windows shortcut != symbolic link. howtogeek.com/howto/16226/…
I downloaded and installed the Oracle SQL Developer to connect to an already existing Oracle 11g database. But it seems that I will need to download and install Oracle client software also, which one do I use if I am on Windows 10?
I do not see the Tnsnanes directory option in my SQL Developer version 1.5.5.
C
Community

This excellent answer to a similar question (that I could not find before, unfortunately) helped me solve the problem.

Copying Content from referenced 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 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.


I know it's been a while, but can you pull out the relevant part of the answer here? Just in case that answer (or even the question) ever gets deleted.
basically it rounds to setting TNS_ADMIN system variable (because SQL Developer searches there ~ first) and, after restarting SQL Developer, choosing New Connection, and in Connection Type dropdown list, instead of Basic, choosing TNS - so - tnsnames is seen alright, but you shouldn't expect the connections to be populated automatically (that was the case with me)
search took me her,so have updated answer since there is already reference to the author @DennisMeng
P
Peter Lang

I had the same problem, tnsnames.ora worked fine for all other tools but SQL Developer would not use it. I tried all the suggestions on the web I could find, including the solutions on the link provided here.
Nothing worked.

It turns out that the database was caching backup copies of tnsnames.ora like tnsnames.ora.bk2, tnsnames09042811AM4501.bak, tnsnames.ora.bk etc. These files were not readable by the average user.

I suspect sqldeveloper is pattern matching for the name and it was trying to read one of these backup copies and couldn't. So it just fails gracefully and shows nothing in drop down list.

The solution is to make all the files readable or delete or move the backup copies out of the Admin directory.


That's probably exactly what you're seeing. SQLPlus and SQL Developer both see/parse/read tnsnames - so if one's bad, that could cascade down and cause your issue.
m
marchelloUA

This helped me:

Posted: 8/12/2011 4:54

Set tnsnames directory tools->Preferences->Database->advanced->Tnsnames Directory

https://forums.oracle.com/forums/thread.jspa?messageID=10020012&#10020012


d
dinvlad

On the newer versions of macOS, one also has to set java.library.path. The easiest/safest way to do that [1] is by creating ~/.sqldeveloper/<version>/sqldeveloper.conf file and populating it as such:

AddVMOption -Djava.library.path=<instant client directory>

[1] https://community.oracle.com/message/14132189#14132189