Use configurationless connections

Below we will discuss how to make a database connection without using tnsnames.ora files:

Using the Easy Connect Naming Method

The Easy Connect naming method eliminates the need for service name lookup in the tnsnames.ora files for TCP/IP environments. In fact, no naming or directory system is required if you use this method.

CONNECT username@[//]host[:port][/service_name][:server][/instance_name]
Enter password: password

The connect identifier converts to the following connect descriptor:

(DESCRIPTION=
 (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=port))
 (CONNECT_DATA=
   (SERVICE_NAME=service_name)
   (SERVER=server)
   (INSTANCE_NAME=instance_name)))

//	Specify // for a URL. For URL or JDBC connections, it is required that the connect identifier is preceded by a double-slash (//). For example: scott@//sales-server Enter password: password For SQL connections, it is optional that the connect identifier is preceded by a double-slash (//). For example, the following connect strings are semantically equivalent: SQL> CONNECT scott@sales-server Enter password: password SQL> CONNECT scott@//sales-server Enter password: password
host	Required. Specify the host name or IP address of the database host computer. The host name is domain-qualified if the local operating system configuration specifies a domain. You may use an IPv4 or IPv6 address as a value. IPv6 addresses or host names that resolve to IPv6 addresses must be enclosed in square brackets, as in [2001:0DB8:0:0::200C:417A] and [salesdb].
port	Optional. Specify the listening port. The default is 1521.
service_name	Optional. Specify the service name of the database. If a user specifies a service name, then the listener connects the user to that specific database. Otherwise, the listener connects to the database specified by the DEFAULT_SERVICE_listener_name parameter in the listener.ora file. If DEFAULT_SERVICE_listener_name is not configured for the listener and a service name is not explicitly specified by the user as part of the Easy Connect syntax, then the listener returns an error.
server	Optional. Specify the database server type to use. This parameter instructs the listener to connect the client to a specific type of service handler. The values for the server parameter are dedicated, shared, and pooled. If server is not specified in the Easy Connect syntax, then the type of server is chosen by the listener (shared server if configured, otherwise a dedicated server is used). Note: In Oracle Call Interface documentation, server is referred to as connect_type.
instance_name	Optional. Used to identify the database instance to access. The instance name can be obtained from the INSTANCE_NAME parameter in the initialization parameter file.

Demo:

@[<net_service_name> | [//]Host[:Port]/<service_name>]

host=collabn1.shaiksameer

service_name=shaikdb.shaiksameer

port=1522

username=test

oracle@collabn1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 16 22:38:53 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SHAIKDB>connect test@'//collabn1.shaiksameer:1522/shaikdb.shaiksameer'

Enter password:

Connected.

or

[oracle@collabn1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 16 22:38:53 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SHAIKDB>connect test@'collabn1.shaiksameer:1522/shaikdb.shaiksameer'

Enter password:

Connected.

SHAIKDB>

or

If the database is listening on the default port then you can ignore the port number during the connection:

[oracle@collabn1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 16 22:41:50 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SHAIKDB>connect system@'//collabn1.shaiksameer/mydb'

Enter password:

Connected.

SHAIKDB>