• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC connection in Oracle db

 
Mira Mehta
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have a simple application; I am not using connection pool & creating connection object on every request & closing the connection after completion of request.
Ideally, number of connection at a time should be number of request at a time to the application; but it's not working like that;
even if there are 5 users; it shows 60 connections sometimes (Status - INACTIVE) from JDBC in Oracle database.

I am thinking to make
connection_object = null;
after closing it (connection_object.close(); ).

But not sure whether it would be helpful or not because close() method releases the db connection immediately.
Is it possible to destroy the connection immediately by connection_object = null; or some other way?


Please let me know if you have any suggestions.

Thanks & Regards...
 
Tim LeMaster
Ranch Hand
Posts: 226
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
5 users doesn't mean 5 connections. A connection will be open and closed each time the hit the server so if 5 users have hit the server 12 times each then 60 connections will have been opened and closed. my guess is this happens pretty fast and oracle hasn't cleaned up its end yet - if you are sure close() is getting called.

Why would you not use a connection pool?
 
Mira Mehta
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reply.
Application was developed in 2001 or 2002; I have introduced few new functions and maintaining the application for the time being.
[ October 27, 2006: Message edited by: Mira Mehta ]
 
Mira Mehta
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

When we say conection_object = null; can't we assume that connection in Oracle db is destroyed?

Please let me know if you have any suggestions.
 
Purushoth Thambu
Ranch Hand
Posts: 425
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you meant after calling connection_object.close() method. Yes it must be closed. If you can provide more information like how did you find out the connection leak to be on the JDBC side? Is the 60 count coming from v$session table? and does the Program and process points to the JDBC as the source?

I assume that the app must be opening connection from more than one place and it's possible in one or two use cases the connection has not be closed. You need to look the app from top to bottom to find out where the connection is leaked. Your DBA's can assist you by firing the below query



The SQL_TEXT won't be complete query. If you need the complete SQL text then you need to join with couple of dynamics views like. The below query will help



I couldn't think of any reason why 60 connections are opened expect that the application must be opening connection from different places. One more possible situation is how your application handles the Exception. Does it close the connection when there is exception?. You have to look the code more.
[ October 28, 2006: Message edited by: Purushothaman Thambu ]
 
Lucas Lee
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you are sure to connections are closed ,and there are no fatel problem occurs,I think you should not worry about it.
 
Mira Mehta
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks friends; thanks for your replies & support.
I went through all java files this weekends; and discovered unclosed connections in few files.
Also I implemented connection_object = null; after connection_object.close().
As you said that I should not worry if above 2 steps have been implemented properly and every where; I am hopefull of postive outcome.

Thanks again...
 
Lucas Lee
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I recommend you to using connection pool such as DBCP in tomcat.
I know there are some useful features in DBCP such as helping programmer to locate the exact code line that open database connection without properly closing.

It help you gain a easier life...
Good luck!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic