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>
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:
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'
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/[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.
hth! Regards, Remco
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
Source: Stackoverflow.com