Oracle Data Provider for .NET / ODP.NET connection strings

Oracle

Using TNS

Data Source=TORCL;User Id=myUsername;Password=myPassword;

Using the Easy Connect Naming Method (aka EZ Connect)

The easy connect naming method enables clients to connect to a database without any configuration.

Data Source=username/password@//myserver:1521/my.service.com;

Port 1521 is used if no port number is specified in the connection string.

Make sure that EZCONNECT is enabled in the sqlnet.ora file. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

'//' in data source is optional and is there to enable URL style hostname values

Specifying Pooling parameters

By default, connection pooling is enabled. This one controls the pooling mechanisms. The connection pooling service creates connection pools by using the ConnectionString property to uniquely identify a pool.

Data Source=myOracle;User Id=myUsername;Password=myPassword;Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;Incr Pool Size=5;Decr Pool Size=2;

The first connection opened creates the connection pool. The service initially creates the number of connections defined by the Min Pool Size parameter.

The Incr Pool Size attribute defines the number of new connections to be created by the connection pooling service when more connections are needed.

When a connection is closed, the connection pooling service determines whether the connection lifetime has exceeded the value of the Connection Lifetime attribute. If so, the connection is closed; otherwise, the connection goes back to the connection pool.

The connection pooling service closes unused connections every 3 minutes. The Decr Pool Size attribute specifies the maximum number of connections that can be closed every 3 minutes.

Restricting Pool size

Use this one if you want to restrict the size of the pool.

Data Source=myOracle;User Id=myUsername;Password=myPassword;Max Pool Size=40;Connection Timeout=60;

The Max Pool Size attribute sets the maximum number of connections for the connection pool. If a new connection is requested, but no connections are available and the limit for Max Pool Size has been reached the connection pooling service waits for the time defined by the Connection Timeout attribute. If the Connection Timeout time has been reached, and there are still no connections available in the pool, the connection pooling service raises an exception indicating that the request has timed-out.

Using Windows user authentication

Oracle can open a connection using Windows user login credentials to authenticate database users.

Data Source=myOracle;User Id=/;

If the Password attribute is provided, it is ignored.

Operating System Authentication is not supported in a .NET stored procedure.

Privileged Connections

Oracle allows database administrators to connect to Oracle Database with either SYSDBA or SYSOPER privileges.

Data Source=myOracle;User Id=myUsername;Password=myPassword;DBA Privilege=SYSDBA;

SYSOPER is also valid for the DBA Privilege attribute.

Runtime Connection Load Balancing

Optimizes connection pooling for RAC database by balancing work requests across RAC instances.

Data Source=myOracle;User Id=myUsername;Password=myPassword;Load Balancing=True;

This feature can only be used against a RAC database and only if pooling is enabled (default).