oracle ORA-12154: TNS: 无法解析指定的连接标识符 (PLSQL Developer)

ORA-12154: TNS:could not resolve the connect identifier specified (PLSQL Developer)

提问by ZakTaccardi

I need to use PLSQL Developer to access oracle databases. I get the following error when I try to connect to my database. ORA-12154: TNS:could not resolve the connect identifier specified.I am able to use SQLPLUS from the command line to connect to the database, and tnspingreturns successfully, but I can't figure out why PLSQL Developer will not work - it's using the right tnsnames.orafile, and the connection string in there is correct because it's the same one that tnspinguses.

我需要使用 PLSQL Developer 来访问 oracle 数据库。当我尝试连接到我的数据库时出现以下错误。ORA-12154: TNS:could not resolve the connect identifier specified.我可以从命令行使用 SQLPLUS 连接到数据库，并且tnsping成功返回，但我无法弄清楚为什么 PLSQL Developer 不起作用 - 它使用了正确的tnsnames.ora文件，以及那里的连接字符串是正确的，因为它与tnsping使用的相同。

Anyone have an idea what I can do to fix this? I've looked at other threads about this specific error with no luck.

任何人都知道我能做些什么来解决这个问题？我已经查看了有关此特定错误的其他线程，但没有运气。

tnsnames.ora

tnsnames.ora

    ORCL =
      (DESCRIPTION = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = MININT-AIVKVBM)(PORT = 1521)) 
      (CONNECT_DATA = 
        (SERVER = DEDICATED) 
        (SERVICE_NAME = orcl)
      )
    )

sqlnet.ora

sqlnet.ora

     SQLNET.AUTHENTICATION_SERVICES= (NTS)
     NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

sqlpus command lineimage here

sqlpus 命令行图像在这里

PLSQL Developer Connection Preferences: image here

PLSQL 开发人员连接首选项：此处为图片

回答by ZakTaccardi

The answer was simply moving the PLSQL Developer folder from the "Program Files (x86) into the "Program Files" folder - weird!

答案只是将 PLSQL Developer 文件夹从“Program Files (x86)”移到“Program Files”文件夹中——很奇怪！

回答by tufy

This error is very common, often the very first one you get on trying to establish a connection to your database. I suggest those 6 steps to fix ORA-12154 :

此错误很常见，通常是您尝试与数据库建立连接时遇到的第一个错误。我建议通过这 6 个步骤来修复 ORA-12154 ：

1. Check instance name has been entered correctly in tnsnames.ora.
2. There should be no control characters at the end of the instance or database name.
3. All paranthesis around the TNS entry should be properly terminated
4. Domain name entry in sqlnet.ora should not be conflicting with full database name.
5. If problem still persists, try to re-create TNS entry in tnsnames.ora.
6. At last you may add new entries using the SQL*Net Easy configuration utility.

1. 检查 tnsnames.ora 中是否正确输入了实例名称。
2. 实例或数据库名称的末尾不应有控制字符。
3. TNS 条目周围的所有括号都应正确终止
4. sqlnet.ora 中的域名条目不应与完整的数据库名称冲突。
5. 如果问题仍然存在，请尝试在 tnsnames.ora 中重新创建 TNS 条目。
6. 最后，您可以使用 SQL*Net Easy 配置实用程序添加新条目。

For more informations : http://turfybot.free.fr/oracle/11g/errors/ORA-12154.html

更多信息：http: //turfybot.free.fr/oracle/11g/errors/ORA-12154.html

回答by Dave Salomon

As I can't add a comment, just thought I'd post this for completion. tufy's answer is correct, it's to do with parenthesis (brackets) in the path to the application being run.

由于我无法添加评论，只是想我会发布此内容以供完成。tufy 的答案是正确的，它与正在运行的应用程序路径中的括号（方括号）有关。

There is an existing networking bug where the networking layer is unable to parse program locations that contain parenthesis in the path to the executable which is attempting to connect to Oracle.

存在一个现有的网络错误，即网络层无法解析在尝试连接到 Oracle 的可执行文件的路径中包含括号的程序位置。

Filed with Oracle, Bug 3807408 refers.

提交给 Oracle，指的是错误 3807408。

Source

来源

回答by khayam

copy paste pl sql developer in program files x86 and program files both. if client is installed in other partition/drive then copy pl sql developer to that drive also. and run from pl sql developer folder instead of desktop shortcut.

在程序文件 x86 和程序文件中复制粘贴 pl sql developer。如果客户端安装在其他分区/驱动器中，那么也将 pl sql developer 复制到该驱动器。并从 pl sql developer 文件夹而不是桌面快捷方式运行。

ultimate solution ! chill

终极解决方案！寒意

回答by sandatomo

I had the same issue with a VM running CentOS7 and Oracle 11GR2 accesible from Windows 7, the solution were weird, at my local machine the tnsnames pointing to the DB had a space before the service name, I just deleted the space and then I was able to connect.

我在运行 CentOS7 和可从 Windows 7 访问的 Oracle 11GR2 的 VM 上遇到了同样的问题，解决方案很奇怪，在我的本地机器上，指向数据库的 tnsnames 在服务名称之前有一个空格，我只是删除了该空格然后我能够连接。

A quick example.

一个简单的例子。

Wrong tnsnames.

错误的域名。

[this is a empty space]XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) )

[这是一个空白]XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) )

EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )

EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO)) )

Right tnsnames.

正确的域名。

XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) )

XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)) )

EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )

EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO)) )

回答by user2960847

I had an issue at work. The oracle server was "patched" and one of the databases I use could not be connect via the TNSNames entry but via Basic connection. The Database was up and admin could see it was up and running.

我在工作中遇到了问题。oracle 服务器已“修补”，我使用的数据库之一无法通过 TNSNames 条目进行连接，而是通过基本连接进行连接。数据库已启动，管理员可以看到它已启动并正在运行。

Addionally any application that used TNS for connecting to the database would not work either.

此外，任何使用 TNS 连接到数据库的应用程序也无法运行。

The problem found was that the database name was not correct in the TNS file but for some reason it's been working for years. Correcting the name fixed it for us. I did find that Oracle SQL Developer kept using the old TNS entry even after I updated it and I don't feel like reinstalling it for just one DB Connection. It appears that when the database was created it was given a smaller name then the others and via some cut and paste action within the TNSNames file it got mixed up. No one is sure how its worked as we're investigating it but the oracle patch ensured that the name had to be correct

发现的问题是 TNS 文件中的数据库名称不正确，但由于某种原因它已经工作多年。更正名称为我们修复了它。我确实发现 Oracle SQL Developer 即使在我更新它之后仍然继续使用旧的 TNS 条目，而且我不想只为一个数据库连接重新安装它。似乎在创建数据库时，它的名称比其他名称小，并且通过 TNSNames 文件中的一些剪切和粘贴操作，它被混淆了。没有人确定它是如何工作的，因为我们正在调查它，但 oracle 补丁确保名称必须正确

An example of the name was it was down as "DBName.Part1.Part2" but in fact the DB name was "DBName"

名称的一个例子是它被降为“DBName.Part1.Part2”，但实际上数据库名称是“DBName”

回答by Alok

JUST copy and paste tnsnames and sqlnet files from Oracle home in PLSQL Developer Main folder. Use below Query to get oracle home

只需将 tnsnames 和 sqlnet 文件从 Oracle home 复制并粘贴到 PLSQL Developer Main 文件夹中。使用下面的查询来获取 oracle 主页

select substr(file_spec, 1, instr(file_spec, '\', -1, 2) -1) ORACLE_HOME from dba_libraries where library_name = 'DBMS_SUMADV_LIB';

select substr(file_spec, 1, instr(file_spec, '\', -1, 2) -1) ORACLE_HOME from dba_libraries where library_name = 'DBMS_SUMADV_LIB';

回答by TallDave

Just wanted to add -- apparently this can also be caused by installing Instant Client for 10, then realizing you want the full install and installing it again in a parallel directory. I don't know why this broke it.

只是想添加 - 显然这也可能是由于安装 Instant Client 10，然后意识到您想要完整安装并在并行目录中再次安装它而引起的。我不知道为什么这打破了它。

回答by venky_ferrari

Also in addition to above solutions, also check the location where the tnsname ora file exists and compare with the path in the environment variable

另外除了上面的解决方案，还要检查tnsname ora文件所在的位置，并与环境变量中的路径进行比较

回答by shareef

• It did not work so i switched to oracle sql developer and it worked with no problems (making connection under 1 minute).
• This link gave me an idea connect to MS Accessso i created a user in oracle sql developer and the tried to connect to it in Toad and it worked.

• 它不起作用，所以我切换到 oracle sql 开发人员并且它没有问题（在 1 分钟内建立连接）。
• 这个链接给了我一个连接到 MS Access的想法，所以我在 oracle sql developer 中创建了一个用户，并尝试在 Toad 中连接到它并且它工作。

Or second solution

或第二种解决方案

you can try to connect using Direct not TNS by providing host and port in the connect screen of Toad

您可以通过在 Toad 的连接屏幕中提供主机和端口来尝试使用 Direct 而不是 TNS 进行连接