oracle SQLPLUS 错误：ORA-12504：在 CONNECT_DATA 中没有给 TNS:listener SERVICE_NAME
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29659264/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLPLUS error:ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
提问by gbritton
I downloaded SQLPLUS from Oracle:
我从 Oracle 下载了 SQLPLUS：
http://www.oracle.com/technetwork/topics/winx64soft-089540.html
http://www.oracle.com/technetwork/topics/winx64soft-089540.html
Basic Lite and SQL*Plus
基本精简版和 SQL*Plus
I then fired up SQL*Plus:
然后我启动了 SQL*Plus：
c:\Program Files\Oracle\instantclient_12_1>sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 15 15:25:36 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
and tried to connect to a database:
并尝试连接到数据库：
connect user\password@hostname
and received the error message:
并收到错误消息：
ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
What am I missing?
我错过了什么？
I ran the queries suggested by Jakub, I got
我运行了 Jakub 建议的查询，我得到了
SQL> select sys_context('USERENV','SERVICE_NAME') from dual;
SYS_CONTEXT('USERENV','SERVICE_NAME')
--------------------------------------------------------------------------------
SYS$USERS
SQL> select sys_context('USERENV','SID') from dual;
SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
877
SQL>
回答by jakub.petr
You're missing service name:
您缺少服务名称：
SQL> connect username/password@hostname:port/SERVICENAME
EDIT
编辑
If you can connect to the database from other computer try running there:
如果您可以从其他计算机连接到数据库，请尝试在那里运行：
select sys_context('USERENV','SERVICE_NAME') from dual
and
和
select sys_context('USERENV','SID') from dual
回答by alexeionin
I ran into the exact same problem under identical circumstances. I don't have the tnsnames.ora file, and I wanted to use SQL*Plus with Easy Connection Identifier format in command line. I solved this problem as follows.
我在相同的情况下遇到了完全相同的问题。我没有 tnsnames.ora 文件，我想在命令行中使用带有 Easy Connection Identifier 格式的 SQL*Plus。我如下解决了这个问题。
The SQL*Plus? User's Guide and Referencegives an example:
在SQL * Plus的？用户指南和参考给出了一个例子：
sqlplus hr@\"sales-server:1521/sales.us.acme.com\"
sqlplus hr@\"sales-server:1521/sales.us.acme.com\"
Pay attention to two important points:
注意两个重要的点：
- The connection identifier is quoted. You have two options:
- You can use SQL*Plus CONNECT command and simply pass quoted string.
- If you want to specify connection parameters on the command line then you must add backslashes as shields before quotes. It instructs the bash to pass quotes into SQL*Plus.
- The service name must be specified in FQDN-form as it configured by your DBA.
- 连接标识符被引用。您有两个选择：
- 您可以使用 SQL*Plus CONNECT 命令并简单地传递带引号的字符串。
- 如果要在命令行上指定连接参数，则必须在引号前添加反斜杠作为屏蔽。它指示 bash 将引号传递给 SQL*Plus。
- 服务名称必须以 FQDN 格式指定，因为它由您的 DBA 配置。
I found these good questions to detect service name via existing connection: 1, 2. Try this query for example:
我发现这些好问题可以通过现有连接检测服务名称：1, 2。试试这个查询，例如：
SELECT value FROM V$SYSTEM_PARAMETER WHERE UPPER(name) = 'SERVICE_NAMES'
回答by Remco
Just a small observation: you keep mentioning conn usr\pass, and this is a typo, right? Cos it should be conn usr/pass. Or is it different on a Unix based OS?
只是一个小小的观察：你一直提到 conn usr\pass，这是一个错字，对吧？因为它应该是 conn usr/pass。还是在基于 Unix 的操作系统上有所不同？
Furthermore, just to be sure: if you use tnsnames, your login string will look different from when you use the login method you started this topic out with.
此外，请确保：如果您使用 tnsnames，您的登录字符串将与您使用本主题开始时使用的登录方法时不同。
tnsnames.ora should be in $ORACLE_HOME$\network\admin. That is the Oracle home on the machine from which you are trying to connect, so in your case your PC. If you have multiple oracle_homes and wish to use only one tnsnames.ora, you can set environment variable tns_admin (e.g. set TNS_ADMIN=c:\oracle\tns), and place tnsnames.ora in that directory.
tnsnames.ora 应该在 $ORACLE_HOME$\network\admin 中。这是您尝试连接的机器上的 Oracle 主目录，在您的情况下是您的 PC。如果您有多个oracle_homes 并且希望只使用一个tnsnames.ora，您可以设置环境变量tns_admin（例如设置TNS_ADMIN=c:\oracle\tns），并将tnsnames.ora 放在该目录中。
Your original method of logging on (usr/[email protected]:port/servicename) should always work. So far I think you have all the info, except for the port number, which I am sure your DBA will be able to give you. If this method still doesn't work, either the server's IP address is not available from your client, or it is a firewall issue (blocking a certain port), or something else not (directly) related to Oracle or SQL*Plus.
您的原始登录方法 (usr/[email protected]:port/servicename) 应该始终有效。到目前为止，我认为您已经掌握了所有信息，但端口号除外，我相信您的 DBA 能够提供给您。如果此方法仍然无效，则可能是您的客户端无法获得服务器的 IP 地址，或者是防火墙问题（阻止了某个端口），或者其他与 Oracle 或 SQL*Plus 无关（直接）的问题。
hth! Regards, Remco
嗯！问候， 雷姆科
