81

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)
  • 1
    even shorter - sqlplus userid/password@database – user44840 Aug 1 '14 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 '14 at 23:02
87
 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)))'
37

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.

  • 1
    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 '16 at 21:51
  • Dead link....... – Harvey Mar 20 at 11:00
  • 1
    @Harvey - updated, thanks. – Alex Poole Mar 20 at 11:06
18

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
    This is a much, much better response – Andrew Sledge Aug 1 '14 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 at 15:30
3

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.