CODEWITHSUNDEEP
javaoracle-databaseswingjdbcplsql
Phreneticus
Phreneticus

Reputation: 381

How to avoid connection leak when program crashes

I'm currently writing a Java application which depends heavily on JDBC and connections to Oracle databases. Whenever I connect to a database, I close the connection in the finally block of my try-catch blocks to avoid connection leaks. This worked fine until now.

But now my program crashed for whatever reason. In PLSQL Developer I can still observe an open JDBC Thin client connection though. My question now is: how can I close a JDBC Connection, when I had to kill my application via task manager ?

Of course this crash shouldn't have happened in the first place, but it's still very unsatisfying that this has caused a connection leak and that I now would have to tell a DBA to kill the JDBC sessions manually, if too many Connections were still open.

Upvotes: 3

Views: 1033

Answers (1)

user2858650
user2858650

Reputation:

Once you've lost the handshake between your application layer and database layer you are going to run into some serious difficulties trying to terminate your database session from the application layer.

With elevated privileges, you might be able to do something fancy where you query the V$SESSION view and try to find your hung session so you can send some kill command using JDBC (note I say might because I can't guarantee a command like that exists in the JDBC drivers - in fact it probably doesn't). But I think that would be serious overkill though.

I think your better option is to tackle the problem from the database side by using Oracle's session management tools. Set the IDLE_TIME parameter in the user profile you are connecting with to something other than UNLIMITED so that Oracle automatically SNIPES (terminates) any connections that are idle for a specified period of time. Talk to your DBA about it and they should be able set this up for you... it's a fairly simple adjustment (though keep in mind you also need the RESOURCE_LIMIT database parameter set to TRUE for any IDLE_TIME setting to be recognized.

Upvotes: 1

Related Questions