How to use sqlplus to connect to an Oracle Database located on another host without modifying my own tnsnames.ora

Question

I want to connect to an oracle database located on another host using sqlplus. This page suggested adding an item on my tnsnames to connect 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@[email protected] ;( I know, this one is not valid)

Answer 1

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

Maybe, and this might depend 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)))'

Answer 2

You can use easy connect for this:

sqlplus usr/[email protected]/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 [email protected]: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.

Answer 3

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

Answer 4

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.