Connect to sql plus from command line using connection string

  • Let's say I have a Oracle database. I have a username = x, password = y, database = z. Also I know the port = a, SID = b, Hostname = c.

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

    sqlplus x/[email protected]'(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)))
    

    Submitting these command lines usually gives me back error messages like:

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

    Many thanks in advance!!

    Assuming you have the right host/port, it sounds to me like the instance isn't running or not registered with the listener.

    using ezconnect: `sqlplus x/[email protected]:a/b`

    for the record, your second example (at least) works for me if I surround it with quotes. like [email protected]"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=c)(PORT=a))(CONNECT_DATA=(SID=b)))"

    Open ->services in window Start-> oracleservicexe It works for me.

  • did you try

    sqlplus username/[email protected]:port/service
    sqlplus x/[email protected]:a/b
    

    modern versions of sqlplus (version 11 or newer) understand this syntax and you don't need a tnsnames.ora file.

    It works perfectly

    which version? Doesn't work for me on CentOS ERROR: ORA-12154: TNS:could not resolve the connect identifier specified

    Default Port Number: 1521

    I have SID instead of service so using ":" instead of "/" worked for me sqlplus username/[email protected]:port:sid

    Works perfectly for me!

    sqlplus that comes with`v11.2.0.1.0` doesn't support `host:port` syntax. Seems `11.2.0.4.0` already supports...

  • 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/[email protected]

    To avoid having a password on the command line see the links in comments here: http://serverfault.com/a/87038/27813

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

    Incorrect: (Your string)

    sqlplus x/[email protected]'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=c)(PORT=a))(CONNECT_DATA=(SID=z)(SERVER=DEDICATED)))'
    

    Correct:

    sqlplus x/[email protected]'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=c)(PORT=a))(CONNECT_DATA=(SID=z)(SERVER=DEDICATED))))'
    
  • 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.

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

  • extract from sqlplus help :

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

      @[<net_service_name> | [//]Host[:Port]/<service_name>]
    
  • I found that it is easy to do remote desktop connection and open the Command Prompt then connect by:

    sqlplus sys/YourPassword as sysdba

  • You should start the services of the your databases. For this, go to Start menu -> Run and start services.msc Then search there for databases services and start it. After that it will start working.

  • This works for me on Oracle 11G

    sqlplus orcl_usr/[email protected]/sid
    

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM