Oracle: Connect to Oracle Databases Without Using Tnsnames.ora

This is a collection of the different ways in which one may connect to Oracle databases without using a tnsnames.ora file. If you're having problems using the EZConnect syntax, make sure your client-side sqlnet.ora has ezconnect as a directory service: NAMES.DIRECTORY_PATH=(TNSNAMES, EZCONNECT)

In this article:

SQL Plus with EZConnect

Linux

  • Code format: sqlplus username@\'hostname/oracle_sid.domain\'
    • Example using zzzz0001 as the username, oracle-hotel-dev.oit.umn.edu as the hostname, and hoteldev.oit as the oracle_sid.domain 
      • sqlplus zzzz0001@\'oracle-hotel-dev.oit.umn.edu/hoteldev.oit\'

Windows

  • Code format:sqlplus "username@'hostname/oracle_sid.domain'"
    • Example using zzzz0001 as the username, oracle-hotel-dev.oit.umn.edu as the hostname, and hoteldev.oit as the oracle_sid.domain 
      • sqlplus "zzzz0001@'oracle-hotel-dev.oit.umn.edu/hoteldev.oit'"

 

SQLcl with EZConnect

Note: With -oci argument required for multi-factor authentication

Linux and Windows

  • Code format: sql -oci username@hostname/oracle_sid.domain
    • Example using zzzz0001 as the username, oracle-hotel-dev.oit.umn.edu as the hostname, and hoteldev.oit as the oracle_sid.domain 

SQL Plus with TWO_TASK / LOCAL environment variable

Linux

  • Code format: export TWO_TASK=//hostname/oracle_sid.domain
    • Example using oracle-hotel-dev.oit.umn.edu as the hostname and hoteldev.oit as the oracle_sid.domain
      • export TWO_TASK=//oracle-hotel-dev.oit.umn.edu/hoteldev.oit
  • Code format:  sqlplus username
    • Example using zzzz0001 as the username: ​​​sqlplus zzzz0001

Windows

  • Code format: set LOCAL=//hostname/oracle_sid.domain
    • Example using dm02-scan.oit.umn.edu as the hostname and htu12dev.oit as the oracle_sid.domain
      • set LOCAL=//dm02-scan.oit.umn.edu/htu12dev.oit
  • Code format:  sqlplus username
    • Example using zzzz0001 as the username: 
      • sqlplus zzzz0001

SQL Plus with TNS connection string

Linux

  • Code format:sqlplus username@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oracle_sid.domain)))'
    • Linux example using zzzz0001 as the username, oracle-hotel-dev.oit.umn.edu as the hostname, and hoteldev.oit as the oracle_sid.domain 
      • sqlplus zzzz0001@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-hotel-dev.oit.umn.edu)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hoteldev.oit)))'

Windows

  • Code format:sqlplus username@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oracle_sid.domain)))
    • Windows example using zzzz0001 as the username, oracle-hotel-dev.oit.umn.edu as the hostname, and hoteldev.oit as the oracle_sid.domain 
      • sqlplus zzzz0001@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-hotel-dev.oit.umn.edu)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hoteldev.oit)))

Database Link

Syntax Option 1

  • Code format:create database link linkname connect to username identified by "password" using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oracle_sid.domain)))' 
  • Example using appuser as the username, agoodpassword​ as the password, oracle-hotel-dev.oit.umn.edu as the hostname, and hoteldev.oit as the oracle_sid.domain 
    • create database link applink connect to appuser identified by "agoodpassword" using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-hotel-dev.oit.umn.edu)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hoteldev.oit)))'

Syntax Option 2

  • Code format:create database link linkname connect to username identified by "password" using '<host>:<port>/<service>';
  • Example using applink as the linkname, appuser as the username, agoodpassword​ as the password, oracle-hotel-dev.oit.umn.edu as the host, 1521 a the port, and hoteldev.oit as the service
    • create database link applink connect to appuser identified by "agoodpassword" using 'oracle-hotel-dev.oit.umn.edu:1521/hoteldev.oit';

expdp/impdp and imp/exp

Linux

  • Code format:expdp username/password@\'server-name:port/service-name\' arg1=X arg2=Y … argN=Z
  • Example using zzzz0001 as the username, dm01-scan.oit.umn.edu as the server-name, 1521 as the port, htu12prd.oit as the service-name. And example arguments for the arg1=X arg2=Y … argN=Z list are parallel=8 and dumpfile=htu12prd_zzzz0001_datetime.dmp parfile=zzzz0001.par  
    • expdp zzzz0001/password@\'dm01-scan.oit.umn.edu:1521/htu12prd.oit\' parallel=8 dumpfile=htu12prd_zzzz0001_datetime.dmp parfile=zzzz0001.par

Note: The data pump utilities will not work without the password supplied on the command like this unless you use an Oracle Wallet credential.

Last modified

TDX ID

8130