I downloaded SQLPLUS from Oracle:
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@hostnameand received the error message:
ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATAWhat 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>5 Answers
You're missing service name:
SQL> connect username/password@hostname:port/SERVICENAMEEDIT
If you can connect to the database from other computer try running there:
select sys_context('USERENV','SERVICE_NAME') from dualand
select sys_context('USERENV','SID') from dualI 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:
- 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.
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/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
1I faced such strange behavior only in one server and only from script it worked when I put env variable TNS_ADMIN despite it was default location network/admin
The following worked for me:
/usr/lib/oracle/21/client64/bin/impdp username/password@aws-rds-hostname:1521/DATABASENAME DIRECTORY=DATA_PUMP_DIR dumpfile=filename.dmp logfile=filename.log full=y
