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