Connection Problem

[Mark Henryson]

Hi,

I am developing an application in JSP/Servlet with Oracle as database. I used a connecion pool for getting the connections. But I frequently encountered the problem "The maximum no of connections exceeds" from the oracle server. Though I took great care in closing the connection using the try-catch-finally block, still I encounter the connection problem.

How to avoid this problem. I also want to look into module by module to see whether the connections are closing properly for each. Any idea/suggestions in this regard will be useful. I heard one sql query is there for finding out how many connections are there, whether it is closed properly or not. If someone knows that sql ,pls tell me.

[Joel McNary]

Hmm..

SELECT * FROM sys.V_$SESSION_CONNECT_INFO

is a place to start.

[Scott Selikoff]

What server are you using? It may be a middle-layer configuration problem. Off hand always closing the connection in a finally block is the best advice for resolving loose connections.

[Mark Henryson]

Hi,
I am using Jrun 4 Server. For database Oracle 9i Server. I am frequently encountering the connection provlem stating that "Exceeds max no of connections - 150". I have to restart the oracle server now then, to resolve the problem .I am closing the connections in the finally loop, however I am encountering the same problem.

I want to see more clearly in which part(since I am using Add, Delete, View and delete part for each module) connections are not closed properly.
I am opening the connection only once and taking the connection from the connection pool. And close it in the finally block.

I am using the same resultset and statment. Whenever I am executing the query using executeUpdate (or) executeQuery, I am put it inside the try and catch block to narrow down the error more specifically. I am using lot of queries. Whether it is adviseable to create the connection, resultset and statment newly or each query and close the connection, resultset and statement for that query then and there.

I don't know the performance tuning in this regard. I am also used like above I mentioned, but what happend If I am fncing more than 70,000 - 1,00,000 records, it is very slow and some time gives eror.

I want to know which is the best way to avoid the connection problems. Any sample coding / site which mention more about the perfomrance tuning for the beginners will be useful.
[ December 11, 2005: Message edited by: Mark Henryson ]