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)

• user44840: even shorter - sqlplus userid/password@database
• Alex Poole: @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?

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)))'

• yannicuLar: if using a portable database, use this instead of sid : (CONNECT_DATA=(SERVICE_NAME=remote_service_name))
• KJH: If the password has special characters in it, you may need to quote it. e.g. 'user/"pass"@...'

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.

• durette: 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.)
• Hrvoje: Dead link.......

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

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

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.