Oracle Client Upgrade from 9 to 10

Question

Hi

Last Friday where i work, an oracle client was upgarded and our IIS server from version 9 to version 10. Now that its on version 10, we are seeing a lot of connections being open up to the database. It is opening up so many connections that we cannot log onto the database using tools like PlSQL developer or Toad. We never had an issue like this when the oracle client was at version 9. Because of the number of clients that exists on this particular box, i dont think it will be possible to revert back to the Oracle 9 client. Is anyone aware of this problem or know of any possible work arounds?

Any help is greatly appreciated

Answer 1

Which connection library are you using? OO4O, ODP, Other?

I'm working from memories of old issues here, so the details are a little fuzzy. With OO4O there are two different ways to initialize the library. One tries to re-use connections more than the other.

In ODP the default is to use connection pooling. Sometimes this leads to extra connections, in case they're needed again. There are some issues with pooled connections that lead me to turn them off. (PL/SQL procedures can hang if called on a dead connection)

If you get more information I'll try to get clarification

Let us know what you find and good luck

Answer 2

Hi Brad

Thanks very much for your response, it was very useful to us. We sent off our issue to Oracle and got the following back

============ This is a known issue discussed in Note:417092.1 Database Connections Are Left Open By Oracle Objects for OLE (OO4O)

Your question: "Does 10g client interface allow the ASP code/class functions the same way as 9i client?"

The workaround for this issue is to implement a loop to remove all the parameters. For example -

for i = 1 to OraDatabase.Parameters.Count OraDatabase.Parameters.Remove(0) next

Bug 5918934 OO4O Leaves Sessions Behind If OraParameters Are Not Removed was logged for this behavior, and has been deemed "not feasible to fix" due to architecture changes required to resolve memory issues.

We did have a loop implemented within our code to remove parameters but on looking at it again, it looks like it is not removing all the parameters. We are currently investigating this.

I will write back to this post once we have identified a solution

Thnaks Damien