CODEWITHSUNDEEP
oracle-databaseconnection-pooling
Yash Dutt
Yash Dutt

Reputation: 197

ORA-02396: exceeded maximum idle time, please connect again error

I have a ASP.Net MVC Application connecting to Oracle DB. I am using LINQ in my controller to pull data from Oracle DB.

If that page is loaded, after several minutes if its idle, it gives the above error.

Now I can't ask my DBA to increase the idle time. In my research I saw mention of Pooling in Web.config file. My understanding is that, because of Pooling, some of these connections are still active. I have removed this portion

Min Pool Size=1;Max Pool Size=20;Pooling=true

Do I have to explicitly say in my Web.config:

Pooling=false

I also have in my Controller, Dispose function as below but that doesn't help:

protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                db.Dispose();
            }

            base.Dispose(disposing);
        }

Please help.

Upvotes: 4

Views: 20959

Answers (3)

Amjad Alloush
Amjad Alloush

Reputation: 1

You don't have to change the Idle_Time, you can append the following to connection string.

Validate Connection=True;

as mentioned in the Oracle documentation "The Validate Connection attribute validates connections coming out of the pool. This attribute should be used only when absolutely necessary, because it causes a round-trip to the database to validate each connection immediately before it is provided to the application. If invalid connections are uncommon, developers can create their own event handler to retrieve and validate a new connection, rather than using the Validate Connection attribute. This generally provides better performance."

Upvotes: 0

O. Jones
O. Jones

Reputation: 108641

Here's an approach. When you resume using your connection after a potential idle delay, such as waiting for an incoming request, do this:

  1. Run some cheap no-op query like SELECT 1 FROM DUAL;
  2. If you get the error you mentioned, make sure the connection is completely closed, then open a new one.
  3. Use the connection as normal.

This is a bit of a hack compared to organizing your connection pool properly, but it's better than opening up a new connection every time you need one.

Upvotes: 0

Harv
Harv

Reputation: 555

If the DBA sets an idle timeout on the server for connections then configure your connection pool with this option.

Min Pool Size=0;

The default is 1. This will keep the ODP.NET client from keeping any open idle connections in the pool while the application is idle. It will still increase the pool size when connection requests come in and it will likely be slightly less efficient at satisfying initial requests since it has to create those connections, but it will work and not have the idle timeout issue.

I agree with some of the comments that there shouldn't be an idle timeout set on the server for these cases, but I've found that some organizations insist on doing this for security reasons.

Upvotes: 1

Related Questions