File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes JDBC connection in Oracle db Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC connection in Oracle db " Watch "JDBC connection in Oracle db " New topic
Author

JDBC connection in Oracle db

Mira Mehta
Greenhorn

Joined: Oct 09, 2006
Posts: 13
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...


Thanks,<br />Mira.
Tim LeMaster
Ranch Hand

Joined: Aug 31, 2006
Posts: 226
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

Joined: Oct 09, 2006
Posts: 13
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

Joined: Oct 09, 2006
Posts: 13
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

Joined: May 24, 2003
Posts: 425
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

Joined: Oct 02, 2006
Posts: 53
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

Joined: Oct 09, 2006
Posts: 13
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

Joined: Oct 02, 2006
Posts: 53
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!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC connection in Oracle db
 
Similar Threads
Welcome R.M. Menon!
weblogic.jdbc.extensions.PoolLimitSQLException: No resources currently available in pool
Re: Closing database connection in destroy metod()?
connection pooling issue in production environment -
JDBC connection in Oracle db