I always prefer to use TNSNAMES.ORA to connect to my database server. It is one of the most convenient ways for database servers with a fixed hostname or IP address. For example, if TNSNAMES.ORA has the following entry:
ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = geek.mydomain.co.in)(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 “geek.mydomain.co.in” on port 1521 “orcl” service when we fire following command like:
C:\Users\nimish.garg>sqlplus scott/tiger@orcl SQL*Plus: Release 22.214.171.124.0 Production on Tue Sep 30 14:24:03 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
There are times when we do not want to modify our TNSNAMES.ORA maybe because we might want to connect a particular database service only for one time. So what are the other ways possible to Connect to Oracle Database without using TNSNAMES.ORA? Well, there are two ways for this:
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:
Any of these forms would work too. Note that the password is supplied on the same line.
SQLPLUS username/password@//hostname:PORT/service_name.example.com SQLPLUS username/password@//hostname/sales.us.example.com <==Expects the default port of 1521 SQLPLUS username/password@//hostname.example.com/service_name.example.com SQLPLUS firstname.lastname@example.org:port/service_name
C:\Users\nimish.garg>sqlplus email@example.com:1521/orcl SQL*Plus: Release 188.8.131.52.0 Production on Tue Sep 30 14:36:00 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Release 184.108.40.206.0 - 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.
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:
In the 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 a double quote.
C:\Users\nimish.garg>sqlplus "scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=geek.mydomain.co.in)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=orcl)))" SQL*Plus: Release 220.127.116.11.0 Production on Tue Sep 30 15:05:13 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Release 18.104.22.168.0 - 64bit Production SQL>
EZCONNECT expects the password to be supplied on the same line. If it is not entered, the EZCONNECT string is not parsed correctly.
SQLPLUS username@//hostname:1521/service_name.example.com password:
This will fail with ORA-12504. It is necessary to use escape \\ surrounding the EZCONNECT string and the use of double quotes:
Or to enter the password on the subsequent line use:
SQLPLUS username@\"username@hostname:PORT/service name\" password: