99

I want to connect to an oracle database located on another host using sqlplus. This page suggested adding an item on my tnsnames to conenct to that database

local_SID =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL= TCP)(Host= hostname.network)(Port= 1521))
    (CONNECT_DATA = (SID = remote_SID))
  )

and then use that in sqlplus

sqlplus user/pass@local_SID

However, in my circumstances modifying the local tnsnames is not possible. Is it possible to connect to a remote database just by using sqlplus argument without having to change tnsnames? Something like

sqlplus user/pass@remote_SID@hostname.network ;( I know, this one is not valid)
2
  • 1
    even shorter - sqlplus userid/password@database
    – user44840
    Aug 1, 2014 at 19:05
  • @GlennLong - but in your version, database is still a TNS alias, which has to exist in the tnsnames.ora, so it's the same as Louis was trying to avoid?
    – Alex Poole
    Aug 1, 2014 at 23:02

4 Answers 4

105
 sqlplus user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))

Maybe, and this might be dependant on the command line environment you're using, you need to quote the string, something like

 sqlplus "user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))"

or

 sqlplus 'user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))'
2
  • 1
    if using a portable database, use this instead of sid : (CONNECT_DATA=(SERVICE_NAME=remote_service_name))
    – yannicuLar
    Feb 14, 2020 at 14:21
  • If the password has special characters in it, you may need to quote it. e.g. 'user/"pass"@...'
    – KJH
    May 25 at 14:26
39

You can use easy connect for this:

sqlplus usr/pass@hostname.network/remote_service_name

To enable easy connect on your machine, you need to add it to the NAMES.DIRECTORY_PATH in sqlnet.ora, e.g.:

NAMES.DIRECTORY_PATH=(EZCONNECT)

If your listener is on a non-default port use ...@hostname.network:port/....

Actually it seems you have to supply a service name, not a SID; they may be the same but if not you'll need to obtain that from the server.

3
  • 2
    If the service name is the same as the hostname, you don't even need to specify the service name when connecting. (Practically nobody does this, but it's nice to know.)
    – durette
    Oct 14, 2016 at 21:51
  • Dead link.......
    – Hrvoje
    Mar 20, 2019 at 11:00
  • 1
    @Harvey - updated, thanks.
    – Alex Poole
    Mar 20, 2019 at 11:06
22

Create a copy of the tnsnames.ora file in a directory that you can write to, modify the file accordingly, then set the TNS_ADMIN environment variable to the location of that directory.

eg:

cp $ORACLE_HOME/network/admin/tnsnames.ora /tmp/tnsnames.ora
# edit the /tmp/tnsnames.ora file to add your entries

# Set the $TNS_ADMIN environment variable so that sqlplus knows where to look 
export TNS_ADMIN=/tmp
2
  • 2
    This is a much, much better response Aug 1, 2014 at 18:57
  • This approach works with a system that has no oracle infrastructure installed other than a sqlplus client. Just copy over the tnsnames.ora from the db server, and follow Phil's process.
    – theRiley
    Jun 7, 2019 at 15:30
5

On Unix/Linux system you can use the user level configuration files to override system level entries.

System-Level         User-Level 
Configuration File   Configuration File
------------------   -------------------
sqlnet.ora           $HOME/.sqlnet.ora
tnsnames.ora         $HOME/.tnsnames.ora

The system-level configuration Files can be found in the directory $TNS_ADMIN . If the variable TNS_ADMIN is not set then they are searched in the directory $ORACLE_HOME/network/admin.

The user-level configuration files don't substitute the system level configuration files as a whole (as the TNS_ADMIN directory substitute the whole $ORACLE_HOME/network/admin directory) but they add to or change entries of the system-level configuration files. If an entry exists in an user-level configuration file then this one is used, if it does not exist in the user-level configuration file then the entry of the system-level configuration file is used.

Not the answer you're looking for? Browse other questions tagged or ask your own question.