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

  • 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/[email protected]_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/[email protected][email protected] ;( I know, this one is not valid)
    

    even shorter - sqlplus userid/[email protected]

    @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?

  •  sqlplus user/[email protected](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/[email protected](DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))"
    

    or

     sqlplus 'user/[email protected](DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))'
    

    if using a portable database, use this instead of sid : `(CONNECT_DATA=(SERVICE_NAME=remote_service_name))`

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

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

    Dead link.......

    @Harvey - updated, thanks.

  • 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
    

    This is a much, much better response

    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.

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

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM