71

Let's say I have an Oracle database. I have a username = x, password = y, database = z, port = a, SID = b, and Hostname = c.

So, how do I connect correctly? I used many options like:

sqlplus x/y@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=c)(PORT=a))(CONNECT_DATA=(SID=z)(SERVER=DEDICATED)))'

sqlplus (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=c)(PORT=a))(CONNECT_DATA=(SID=b)))

But they usually give me the following errors:

ORA-12560: TNS:protocol adapter error
ORA-12514: TNS:listener does not currently know of service

Improve this question
3
  • 3
    Assuming you have the right host/port, it sounds to me like the instance isn't running or not registered with the listener. Commented May 13, 2014 at 11:44
  • 2
    using ezconnect: sqlplus x/y@c:a/b Commented May 14, 2014 at 15:45
  • 1
    for the record, your second example (at least) works for me if I surround it with quotes. like x@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=c)(PORT=a))(CONNECT_DATA=(SID=b)))" Commented Feb 18, 2015 at 22:04

7 Answers 7

Reset to default
119

Did you try:

sqlplus username/password@host:port/service
sqlplus x/y@c:a/b

Modern versions of sqlplus (11 and higher) understand this syntax and you don't need a tnsnames.ora file.

Improve this answer
4
  • 1
    Default Port Number: 1521 Commented Aug 31, 2016 at 7:28
  • 4
    I have SID instead of service so using ":" instead of "/" worked for me sqlplus username/password@host:port:sid Commented Mar 27, 2017 at 9:04
  • 1
    sqlplus that comes withv11.2.0.1.0 doesn't support host:port syntax. Seems 11.2.0.4.0 already supports... Commented Jan 20, 2019 at 18:59
  • 1
    Shame on me for being at the mercy of tnsnames.ora. Zero deps forever Commented Feb 22, 2024 at 21:36
13

The most simple is to use tnsnames.ora file to connect to the database. For that edit it and add a new entry: This file normally resides in the $ORACLE HOME\NETWORK\ADMIN directory.

myDb  =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = c)(Port =a))
   )
 (CONNECT_DATA =
   (SERVICE_NAME =b)
 )
)

and then you could connect to the db:

sqlplus x/y@myDb

Improve this answer
1
  • To avoid having a password on the command line see the links in comments here: serverfault.com/a/87038/27813 Commented Feb 18, 2015 at 21:06
10

To original poster... in the first option you missed a closing parenthesis

Incorrect: (Your string)

sqlplus x/y@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=c)(PORT=a))(CONNECT_DATA=(SID=z)(SERVER=DEDICATED)))'

Correct:

sqlplus x/y@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=c)(PORT=a))(CONNECT_DATA=(SID=z)(SERVER=DEDICATED))))'
Improve this answer
4

SQL*Plus 19.9.0.0 shows this syntax

Usage: CONN[ECT] [{logon|/|proxy} 
    [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
  <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]

If you use CMD window do

sqlplus /nolog 
connect sys/pwd@service as sysdba

also, I suggest not to include password into conn string. Enter it on the second prompt. This way it is not visible. And also, for Ora19 this is the connection syntax I was able to use successfully, without tnsnames

C:\WINDOWS\system32>sqlplus /nolog 
SQL> connect sys@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhostname)(PORT=1522))
 (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myServiceName)))' as sysdba
Enter Password:
Connected.
SQL>
Improve this answer
2

Your sqlplus line looks correct, verify the following:

  1. You can connect as sysdba on the database server itself.
  2. You can connect as the user you are trying to on the database server itself.
  3. You can ping the database server from the computer you are trying to connect from.
  4. You can tnsping the listener from the computer you are trying to connect from.

If all these check out you may want to create a fresh connection line to make sure you don't have a typo.

Improve this answer
1

Maybe your database is not up. If the machine was restarted and the instance is not set to autostart (and it was not started manually), you may need to start the service yourself.

If you have access to the Services screen, you can do it from there; or, you can do it from the command line.

Go to Command prompt and enter the following commands:

set oracle_sid=ORCL

net start oracleserviceORCL

The first sets the Oracle SID; the second actually starts the service.

Improve this answer
0

extract from sqlplus help :

can be in the form of Net Service Name or Easy Connect.

  @[<net_service_name> | [//]Host[:Port]/<service_name>]
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.