Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
even shorter - sqlplus userid/password@database –  Glenn Long Aug 1 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 at 23:02

3 Answers 3

up vote 15 down vote accepted
 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)))'
share|improve this answer

You can use easy connect for this:

sqlplus usr/pass@hostname.network/remote_service_name

If your listener is on a non-default port use ...@hostname.network:port/....

Edit: 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.

share|improve this answer

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 $THS_ADMIN environment variable so that sqlplus knows where to look 
export TNS_ADMIN=/tmp
share|improve this answer
1  
This is a much, much better response –  Andrew Sledge Aug 1 at 18:57

protected by Paul White Aug 1 at 19:14

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.