EZCONNECT is the easy connect protocol, available in 10g, whenever you want to connect to a database without tnsnames and without ldap.
$ grep -iw directory_path $TNS_ADMIN/sqlnet.ora
names.directory_path=EZCONNECT
$ sqlplus scott/tiger@//srv01:1521/db01
connect to server srv01 on port 1521 for service db01
HOSTNAME was the old-fashion way to connect to a database, where hostname = sid and port = 1521. In this regard EZCONNECT is just an extension of the hostname method.
Typical HOSTNAME usage, that is the same as EZCONNECT with default port 1521.
sqlplus scott/tiger@db01
connect to server db01 on port 1521 for service db01
There is a behavior change between 10g and 11g. In 10g, the default service name defaulted to the DNS alias used to connect. In 11g, the default is null.
$ nslookup db01
Server: ns001.example.com
Address: 198.0.0.30
Name: srv01.example.com
Address: 198.0.0.60
Aliases: db01.example.com
$ nslookup db02
Server: ns001.example.com
Address: 198.0.0.30
Name: srv01.example.com
Address: 198.0.0.60
Aliases: db02.example.com
Both DB01 and DB02 DNS aliases point to the same server.
Let’s try with 10g
$ sqlplus -L scott/[email protected]
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Feb 7 15:46:53 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select global_name from global_name;
GLOBAL_NAME
---------------------------------------
DB01.EXAMPLE.COM
SQL> quit
$ sqlplus -L scott/[email protected]
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Feb 7 15:47:33 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select global_name from global_name;
GLOBAL_NAME
---------------------------------------
DB02.EXAMPLE.COM
Let’s try with 11g sqlplus
$ sqlplus -L scott/[email protected]
SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 7 15:50:27 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
It no longer works. Period. This is documented as Problem 556996.1 in Metalink.
A 10g tnsping will reveal
$ tnsping db01.example.com:1521
TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production on 07-FEB-2011 15:52:34
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
/home/lsc/sqlnet.ora
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=db01.example.com))(ADDRESS=(PROTOCOL=TCP)(HOST=198.0.0.60)(PORT=1521)))
OK (80 msec)
In 10g the service_name is the connection dns alias used
In contrary, the 11g tnsping service name is null
$ tnsping db01.example.com:1521
TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 07-FEB-2011 15:56:55
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
/home/lsc/sqlnet.ora
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=198.0.0.60)(PORT=1521)))
OK (10 msec)
The tnsping works, but the service_name is empty.
How to fix this?
1) you specify the SID in easy connect (yes, this is easy!)
$ tnsping db01.example.com:1521/db01.example.com
TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 07-FEB-2011 15:59:10
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
/home/lsc/sqlnet.ora
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=db01.example.com))(ADDRESS=(PROTOCOL=TCP)(HOST=198.0.0.60)(PORT=1521)))
OK (10 msec)
2) you use 10g, or 10g behavior in 11g with patch 9271246 (available only on a limited number of plateforms, os and db versions),
3) you specify a default service for your listener
$ vi listener.ora
DEFAULT_SERVER_LISTENER=DB01
$ lsnrctl reload
$ sqlplus -L scott/tiger@db01
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
This is a bit confusing as if you are servicing more than one database per listener, all dns aliases will default to the same database. So I would not recommend a default service name if there is more than one service name.