EZCONNECT, TNSNAMES by example

General
The post below describes how to use EZCONNECT, with examples.

Configuration:
On the client side, enable EZCONNECT protocol by editing sqlnet.ora:

NAMES.DIRECTORY_PATH=(ezconnect, tnsnames)

Usage:

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

• // - Optional. Specify // for a URL and jdbc connection

• host - Required. Specify the host name or IP address of the database server computer.
• port - Optional. Specify the listening port. The default is 1521.
• service_name - Optional. Specify the service name of the database.
                            Default value for service name, when not provided, is the host of the database server computer

How to find service name:
Option A - check the listener.ora on the Oracle host server.
Option B - check the output of lsnrctl status listener_name

For example:
user@my_host:/software/oracle/101/network/admin>% lsnrctl status lsnr_inst1

LSNRCTL for Linux: Version 10.1.0.5.0 - Production on 13-AUG-2014 06:26:02

Copyright (c) 1991, 2004, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=my_host)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     lsnr_inst1
Version                   TNSLSNR for Linux: Version 10.1.0.5.0 - Production
Start Date                04-JUN-2013 11:22:50
Uptime                    248 days 13 hr. 13 min. 56 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /software/oracle/101/network/admin/listener.ora
Listener Log File         /software/oracle/101/network/log/lsnr_inst1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=my_host.my_domain.com)(PORT=1521)))
Services Summary...
Service "MYDBC" has 1 instance(s).
  Instance "hsodbc", status UNKNOWN, has 1 handler(s) for this service...
Service "srv1" has 2 instance(s).
  Instance "srv1", status UNKNOWN, has 1 handler(s) for this service...
  Instance "srv1", status READY, has 1 handler(s) for this service...
Service "srv2" has 2 instance(s).
  Instance "srv2", status UNKNOWN, has 1 handler(s) for this service...
  Instance "srv2", status READY, has 1 handler(s) for this service...
The command completed successfully


Compare this to listener.ora contents:

# listener.ora Network Configuration File: /software/oracle/101/network/admin/listener.ora
# Generated by Oracle configuration tools.

LSNR_INST1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = my_host)(PORT = 1521))
       )
    )
  )

SID_LIST_LSNR_INST1 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = srv1)
      (ORACLE_HOME = /software/oracle/101)
      (SID_NAME = sid_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = srv2)
      (ORACLE_HOME = /software/oracle/101)
      (SID_NAME = sid_2)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = MYDBC)
      (ORACLE_HOME = /software/oracle/101)
      (SID_NAME = sid_3)
    )
  )


Using EZconnect in SQL Developer
In Database field, put host:port/service_name

Full connection string
Another alternative to avoid tnsnames.ora, is to provide full connection string, and not the tnsnames.ora alias.
Note:
No spaces in the connection string.
The " around the connection string.
sqlplus user/password@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=my_host.my_domain.com)(PORT=1521))(CONNECT_DATA=(SID=sid_1)))"

Oracle Client version limitation
Note - EZCONNECT is available only on Oracle Client 10 and higher.

TNSNAMES
It is possible to split tnsnames.ora into several files:
This is the tnsnames.ora file:
IFILE=c:\oracle\ora92\network\ADMIN\SITE_A_tnsnames.ora
IFILE=c:\oracle\ora92\network\ADMIN\SITE_B_tnsnames.ora

And each  tnsnames.ora would look like a regular tnsnames.ora file. 
For example:           
CONN_STR_A=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST= 88.8.88.888)(PORT = 1521))(CONNECT_DATA = (SID = orainst)))
CONN_STR_B=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST= 99.9.99.999)(PORT = 1521))(CONNECT_DATA = (SID = orainst)))

Appendix
Oracle Reference