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

Answer 1

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

Answer 2

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:

1. 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.
2. 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'