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

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

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.


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

Tags:

Oracle

Sqlplus