database 向 SQL Developer 添加新连接时未显示 Oracle TNS 名称

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

提问by Americus

I'm trying to connect to an oracle database with SQL Developer.

我正在尝试使用 SQL Developer 连接到 oracle 数据库。

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

我已经安装了 .Net oracle 驱动程序并将tnsnames.ora文件放在
C:\Oracle\product\11.1.0\client_1\Network\Admin

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

我在 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.

在 SQL Developer 中，当我尝试创建新连接时，没有 TNS 名称显示为选项。

Is there something I'm missing?

有什么我想念的吗？

回答by JaseAnderson

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

SQL Developer 将按此顺序在以下位置查找 tnsnames.ora 文件

1. $HOME/.tnsnames.ora
2. $TNS_ADMIN/tnsnames.ora
3. TNS_ADMIN 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

1. $HOME/.tnsnames.ora
2. $TNS_ADMIN/tnsnames.ora
3. 注册表中的 TNS_ADMIN 查找键
4. /etc/tnsnames.ora（非windows）
5. $ORACLE_HOME/network/admin/tnsnames.ora
6. LocalMachine\SOFTWARE\ORACLE\ORACLE_HOME_KEY
7. LocalMachine\SOFTWARE\ORACLE\ORACLE_HOME

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

要查看哪个 SQL Developer 正在使用，请show tns在工作表中发出命令

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

如果您的 tnsnames.ora 文件未被识别，请使用以下步骤：

1. Define an environmental variable called TNS_ADMINto 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_ADMINvariable in the .profile file in your home directory.

2. Confirm the os is recognizing this environmental variable

   From the Windows command line: echo %TNS_ADMIN%

   From linux: echo $TNS_ADMIN

3. Restart SQL Developer

4. Now in SQL Developer right click on Connectionsand select New Connection.... Select TNSas connection type in the drop down box. Your entries from tnsnames.ora should now display here.

1. 定义一个名为TNS_ADMIN的环境变量以指向包含 tnsnames.ora 文件的文件夹。

   在 Windows 中，这是通过导航到控制面板>系统>高级系统设置>环境变量...

   在 Linux 中，在主目录的 .profile 文件中定义TNS_ADMIN变量。

2. 确认操作系统正在识别这个环境变量

   从 Windows 命令行：echo %TNS_ADMIN%

   来自 linux: echo $TNS_ADMIN

3. 重新启动 SQL Developer

4. 现在在 SQL Developer 中右键单击Connections并选择New Connection...。在下拉框中选择TNS作为连接类型。您来自 tnsnames.ora 的条目现在应该显示在这里。

回答by Paul Mattey

Open SQL Developer. Go to Tools -> Preferences -> Databases -> Advanced Then explicitly set the Tnsnames Directory

打开 SQL 开发人员。转到 Tools -> Preferences -> Databases -> Advanced 然后显式设置 Tnsnames 目录

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.

我的 TNSNAMES 设置正确，我可以连接到 Toad、SQL*Plus 等，但我需要这样做才能让 SQL Developer 工作。也许这是一个 Win 7 问题，因为安装起来也很痛苦。

回答by Chandra Sekhar

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



In the Preferencesoptions expand Database --> select Advanced --> under "Tnsnames Directory" --> Browse the directorywhere tnsnames.orapresent.
Then click on Ok.
as shown in below diagram.

在SQLDeveloper 中浏览Tools --> Preferences，如下图所示。



在tnsnames.ora存在的首选项选项expand Database --> select Advanced --> under "Tnsnames Directory" --> Browse the directory中。然后点击确定。 如下图所示。

You have Done!

你已经完成了！

Now you can connect via the TNSnamesoptions.

现在您可以通过TNSnames选项进行连接。

回答by DCookie

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

通过运行 TNSPING 来检查连接（9i 或更高版本），您始终可以找到正在使用的 tnsnames.ora 文件的位置：

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.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.

有时，问题在于您在 tnsnames.ora 中创建的条目，而不是系统找不到它。也就是说，我同意设置 tns_admin 环境变量是一件好事，因为它避免了在确定在具有多个 oracle home 的系统中使用哪个 tnsnames 文件时不可避免的问题。

回答by Allan Stewart

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?

Jason 提到的步骤非常好，应该可以工作。不过，SQL Developer 有一些小问题。它在第一次读取 tnsnames.ora 文件时缓存连接规范（主机、服务名称、端口）。然后，当从 tnsname.ora 文件中删除原始条目时，它不会使规范无效。即使在终止并重新启动 SQL Developer 之后，缓存仍然存在。这不是处理这种情况的不合逻辑的方式。即使 tnsnames.ora 文件暂时不可用，只要原始规范仍然正确，SQL Developer 仍然可以建立连接。问题在于他们的下一个小转折。解析连接时，SQL Developer 将 tnsnames.ora 文件中的服务名称视为区分大小写的值。因此，如果您曾经有一个条目名称 ABCD。world 并且您将其替换为一个名为 abcd.world 的新条目，SQL Developer 不会更新其 ABCD.world 的连接规范 - 它会将 abcd.world 视为完全不同的连接。为什么我对 Oracle 产品将 Oracle 开发的明确不区分大小写的文件格式的内容视为区分大小写并不感到惊讶？

回答by B Chowdhary

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

在Sql Developer中，导航到Tools->preferences->Datababae->advanced->Set Tnsname directory到包含tnsnames.ora的目录

回答by Michael

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.

上述更改对我的情况没有任何影响。我可以在命令窗口中运行 TNS_PING 但 SQL Developer 无法弄清楚 tnsnames.ora 在哪里。

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\ .

我的问题（Windows 7 - 64 位 - Enterprise）是 Oracle 安装程序将开始菜单快捷方式指向错误版本的 SQL Developer。安装程序似乎附带了三个 SQL Developer 实例。一个在 %ORACLE_HOME%\client_1\sqldeveloper\ 中，两个在 %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.

安装程序安装了一个开始菜单快捷方式，指向 bin 目录中一个根本不起作用的版本。每次启动 SQL Developer 时它都会要求输入密码，不记得当我选择 TNS 作为连接机制时所做的选择并显示一个空白列表。它也没有在其他帖子中引用的数据库高级设置中的 TNS 目录字段。

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.

我扔掉了旧的 Start 快捷方式并安装了一个快捷方式到 %ORACLE_HOME%\client_1\sqldeveloper\sqldeveloper.exe 。在我的情况下，该更改解决了问题。