3

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.

Phreneticus
  • 349
  • 2
  • 11

1 Answers1

1

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.

  • Thanks, talking to a DBA about that does indeed seem a good idea. The only problem might be that whenever my program will run outside of our company (it is an API testing tool and might be interesting for our clients) , they will face the same problems and will have to make the same adjustments to their databases, which might make them suspicious about the stability of the program ^^ – Phreneticus Aug 25 '17 at 07:38
  • I wouldn't worry too much about sending the wrong signals. Setting the `IDLE_TIME` is a good connection pooling practice especially if you have large groups of users accessing the database. A group of users could just as easily bog down the database by opening connections and then walking away from their machine without closing the program. –  Aug 25 '17 at 16:13