Connect Oracle database without tnsnames.ora

Posted on by

Q: How to connect sqlplus without tnsnames.ora

Q: Connect Oracle database without tnsnames.ora

Q: Multiple methods to connect oracle database.

Q: Connect Oracle database directly from command prompt.

 

Normally you are using TNSNAMES.ORA which is located in the \OraHome\network\ADMIN  and this is one of the most convenient way for the database servers who HOST Name or IP is fixed. If TNSNAMES.ORA has following entry

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

TNS looks into “TNSNAMES.ORA” file to find out the meaning of “orcl” and connects Oracle Database Server Machine “192.168.1.1” on port 1521 “orcl” service when we fire following command like

C:\Users\fs>sqlplus scott/tiger@orcl

Connected to:
Oracle Database 12g Enterprise Edition


SQL>---

Sometimes we do not want to modify our TNSNAMES.ORA may be because we might want to connect a particular database service only for one time. So what are the other way possible to Connect to Oracle Database without using TNSNAMES.ORA? There are two ways for this:

1) EZCONNECT

EZCONNECT is Oracle’s easy connect naming method.  EZCONNECT eliminates the need for service name lookups in TNSNAMES.ORA files when connecting to an Oracle database across a TCP/IP network.

Syntax of EZCONNECT:

sqlplus username/password@[//]host[:port][/service_name]

Example:

C:\Users\fs>sqlplus scott/tiger@192.168.1.1:1521/orcl

Connected to:
Oracle Database 12g Release 12.- 64bit Production

SQL>

To enable EZCONNECT “sqlnet.ora” should have naming methods “ezconnect” specified in NAMES.DIRECTORY_PATH Parameter. “sqlnet.ora” is located in $ORACLE_HOME/network/admin

Example:

NAMES.DIRECTORY_PATH=(ezconnect, tnsnames)

2) TNS Connect String
The TNS Connect String also known as Connect Descriptor is a type of connect identifier. It defines the parameters that need the Oracle Net Service to connect to a database service

Syntax of TNS Connect String

sqlplus "username/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=port))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=servicename)))"

in above syntax lower case letters must be replaced with the actual values. Please remember there should not be any space in the connect string, also no carriage return and in UNIX put the single quote instead of double quote.

Example

C:\Users\fs>sqlplus "scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.1)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=orcl)))"

Connected to:
Oracle Database 12g Release 11.2.0.3.0 - 64bit Production

SQL>