SQLPLUS error ORA-12504 TNS listener was not given the SERVICE NAME in CONNECT DATA

Question

I downloaded SQLPLUS from Oracle   http   www oracle com technetwork topics winx64soft-089540 html  Basic Lite and SQL Plus  I then fired up SQL Plus   c  Program Files Oracle instantclient 12 1 gt 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  SQL gt  select sys context  USERENV   SERVICE NAME   from dual   SYS CONTEXT  USERENV   SERVICE NAME   --------------------------------------------------------------------------------  SYS USERS  SQL gt  select sys context  USERENV   SID   from dual   SYS CONTEXT  USERENV   SID   --------------------------------------------------------------------------------  877  SQL gt

User · Answer

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.

The SQL*Plus® User's Guide and Reference gives an example:

sqlplus hr@\"sales-server:1521/sales.us.acme.com\"

Pay attention to two important points:

The connection identifier is quoted. You have two options:
1. You can use SQL*Plus CONNECT command and simply pass quoted string.
2. 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.

I found these good questions to detect service name via existing connection: 1, 2. Try this query for example:

SELECT value FROM V$SYSTEM_PARAMETER WHERE UPPER(name) = 'SERVICE_NAMES'

User · Answer

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   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 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   Your original method of logging on  usr pwd 10 15 240 13 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   hth  Regards  Remco

User · Answer

You re missing service name    SQL gt  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

[oracle] SQLPLUS error:ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Examples related to oracle

Examples related to connect

Examples related to sqlplus