Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Wednesday, September 16, 2015

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>

No comments: