How to connect Oracle without tnsnames.ora

There is always a question in mind that how to connect to the oracle?I already given information about Oracle database management system in my previous articles.In this article i would like to explain how to connect to the oracle without tnsnames.ora file.The convenient way to connect to oracle is with TNSNAMES.ORA but most of the interviewers will ask about the TNSNAMES.ORA and these kind of tricky questions.

How to connect to Oracle without tnsnames.ora :

In this section i would like to provide more information about how to connect to Oracle without tnsnames.ora. Before that we always require to check that how the tnsnames.ora can connect to oracle. If host name and ip address is fixed then you can conveniently connect to the oracle database.

The Following Entry is very important entry in tnsnames.ora file,

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

We require to connect to the oracle using following command,

C:\Users\Amit.S>sqlplus scott/tiger@orcl
SQL*Plus: Release 11.2.0.3.0 Production on Tue aug 12 17:34:03 2020
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Why we require to connect without tnsnames.ora ?

For connections we require to always modify the tnsnames.ora file and all new users or hosts.There are so many times we require to connect to particular database service for one time.In that situations we may require to connect without tnsnames.ora.

There are following ways to connect to oracle witout tnsnames.ora.

Method 1 : Using EZCONNECT

The easy way to connect to oracle without tnsnames.ora is using EZCONNECT or you can say Oracl’ easy connect naming method.We can connect to oracle database across TCP/IP network.

Syntax :

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

Example to connect using EZCONNECT :

C:\Users\Amit.S>sqlplus scott/192.168.0.119:1521/orcl
SQL*Plus: Release 11.2.0.3.0 Production on Tue aug 12 17:34:03 2020
Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL>

Note to connect through EZCONNECT :

To enable EZCONNECT method we require to add naming method as “ezconnect. We require to specify the method name in NAMES.DIRECTORY_PATH parameter of “sqlnet.ora”

Location of sqlnet.ora file :

$ORACLE_HOME/network/admin

Example to add ezconnect method :

NAMES.DIRECTORY_PATH=(ezconnect, tnsnames)

Method 2 : Using TNS Connection string

We can connect to oracle using connect descriptor or connection string you can say that as connect identifier.These are nothing but the parameters which need the oracle net service to connect to the oracle database service.

The following is the syntax of connection string :

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

The following strings needs to be added in above syntax :

username/password : This is user name and password of oracle.

hostname : Hostname is nothing but the ip address or host name of server.

Port : The oracle port is 1521

Server : You require to give the type of server.

Example

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

SQL*Plus: Release 11.2.0.3.0 Production on Tue aug 12 16:49:13 2020
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

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

SQL>

These are some methods which can answer about -How to connect Oracle without tnsnames.ora . If you like this article or if you have any issues with the same you can comment in comments section.