How to set tnsnames.ora location for SQL Developer in Windows 10

3
votes

This question (Use tnsnames.ora in Oracle SQL Developer) looks like it should have the answer. But my copy of SQL Developer does not have Advanced under Database (it does have Advanced Parameters, but that's not helping).

I've looked all through the preferences (and help), and can't find where to set it. The accepted answer has a nice picture and this:

In the Preferences options expand Database --> select Advanced --> under "Tnsnames Directory" --> Browse the directory where tnsnames.ora present.

My picture mostly matches, but there is no Tnsnames Directory option at the bottom.

This link (http://www.oracle.com/technetwork/issue-archive/2008/08-may/o38sql-102034.html) gives the following useful information:

A TNS connection uses an alias entry from a tnsnames.ora file. Oracle SQL Developer uses only one tnsnames.ora file. You may have more than one on your local machine or want to use the tnsnames.ora file on a remote machine, so note that Oracle SQL Developer looks sequentially for the tnsnames.ora file in the following locations:

  1. $HOME/.tnsnames.ora
  2. $TNS_ADMIN/tnsnames.ora
  3. /etc/tnsnames.ora (non-Windows systems)
  4. $ORACLE_HOME/network/admin/tnsnames.ora
  5. Registry key

I want the $ORACLE_HOME option to be used, but there appears to be a copy in one of the first 2 locations. And I'm not sure where $HOME or $TNS_ADMIN is defined (so I could just copy over my good copy there), or better yet, how to re-define it to use the one in $ORACLE_HOME.

1
oraclewindows-10oracle-sqldevelopertnsnames
You don't really need tnsnames when using SQL Developer. Why do you want to use it? – a_horse_with_no_name
@a_horse_with_no_name - probably because I'm a TOAD user, so that's what I'm used to. But your comment was enough for me to try the connection information I'd been given another way, and I've finally figured what goes where to make a basic connection. I'm supposed to be comparing the two, so the information is probably still useful. – thursdaysgeek
$ORACLE_HOME and $TNS_ADMIN are environment variables. Google for "change environment variables in Windows 10" - you will get many results. – Wernfried Domscheit

1 Answers

1
votes

The sequence of checks you mentioned is correct. To see what the tool is using there's 2 choices

1 - In a worksheet type "show tns"

SQL> show tns
TNS Lookup locations
--------------------

Location used:
-------------
    /Users/klrice

Available TNS Entries
---------------------
SQL>

2 - Also in the worksheet issue this command. 

setloglevel oracle.dbtools.raptor.utils INFO

Blog post with details here : http://krisrice.io/2008-04-16-it-seems-to-come-up-often-that-some/

enter image description here