Connecting to an Oracle Database

Summary

Almost all client applications that need to use an Oracle database will have a login window of some type. Usually they will ask for a Username, a Password, and have a field labeled "Database" or "Server". This field generally causes the most trouble because it can be used in a number of different ways depending on your Oracle Client and configuration. In general there will be a database administrator or IT person who can answer your questions on what to use.

Using a database alias:

The most common thing to enter in the "Database" field is a database alias. A database alias is usually a simple single word identifier for a particular database. An example of a database alias would be "MyTestDB" which would connect to a test database somewhere on the network. The alias has to have been created in a file called "TNSNames.ora". The TNSNames.ora file contains a list of database aliases and corresponding information about that database (such as the databases IP address, port number and instance name). Often a company will have a standard TNSNames.ora file which is either centrally located on the network or copied down to each client machine. A common problem is when the Oracle Client can't find your TNSNames.ora file (or finds a different one that doesn't contain your alias or has incorrect/older information.) Please see "TNSNames.ora and it's location".

EZConnect:

This connection method was introduced in Oracle 10 and allows a fairly simple connection syntax which doesn't rely on TNSNames.ora at all. The syntax of EZConnect is:

//hostname or ip address/database instance

A simple example would be:

//mydbserver/orcl

This would connect to the machine "mydbserver" using the default port of 1521 and using the instance name "orcl". If you don't know the instance name, check with your database administrator.

A more complex example using an IP address instead of a hostname and a nonstandard port would be:

//192.168.1.52:2067/orcl.company.com

The Oracle Instant Client has the EZConnect feature enabled by default. Other Oracle Clients use a file called sqlnet.ora to set this. To use EZConnect on these clients make sure that the sqlnet.ora setting "NAMES.DIRECTORY_PATH" includes an entry for EZCONNECT. A sample of this entry would be:

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

This setting allows either a database alias through tnsnames.ora or an EZConnect string.