How to prevent your SQL Developer sessions from being killed

A while back I worked on a site where the network would terminate connections that were idle too long (around 20-30 minutes). It is pretty frustrating when you've been working on something for a while then you go grab a coffee or have a meeting and come back to find the connection had been killed. It was even more frustrating when the session was still alive in the database and had to be killed there, so that it could rollback the work so that it could be repeated all over again.


What I'd like is a SQL Developer feature that would periodically send a lightweight request over to the database so the connection is kept alive. Alas, the powers that be seem averse to providing such a function.


I also accept that there are barriers in trying to get people to change their opinions.


The good news is that SQL Developer has the concept of extensions. 
If something you want is too specialised to be included by Oracle themselves, or they haven't got around to it or simply don't want to do it, then an extension can help.


In this case, a bloke called MinChen Chai has published an extension to add KeepAlive functionality to SQL Developer.


Just download it and add the .jar to the directory sqldeveloper\sqldeveloper\extensions and the Right-click menu on a connection will include a Keep-Alive item. 


If you want to be on the cautious side, you can use unzip or similar to extract the class files from the JAR file. Then you can use the Java Decompiler (or similar) and view the source from the class files. The SQL used is in the Pinger.class and is a pretty inoffensive SELECT SYSDATE FROM DUAL. 


Warning : I haven't tested to see if it works if you spawn out an unshared worksheet.


If your environment is locked down to prevent extensions being added, then all I can suggest is a User-Defined Report with a refresh set to the maximum delay. Unfortunately this grabs focus in Windows and the maximum delay is just two minutes which is much too frequent in my opinion, but it is hard coded. As an aside, the 'reports' component also sits in the extensions directory (oracle.sqldeveloper.report.jar).


Should we be doing this ?


I accept there are situations where it is appropriate for a DBA to terminate sessions. And this won't stop an explicit ALTER SYSTEM KILL SESSION.


However I think terminating sessions automatically based on 'being idle' is something that is abnormal, and is inappropriate for most environments.  
Before terminating a session, several factors should be considered. These include 

• Is the session actually doing something important - like the monthly pay cheques
• Is the session blocking other sessions (and which session is more vital to the business)
• Is there a need to preserve session state (PL/SQL global variables, temporary tables etc)
• Is there an active transaction
• Can the transaction be (safely) committed
• How much rollback would need to be applied if it is killed
• Is the session consuming lots of memory
• Can the user/client cope gracefully with a failure

If your DBA doesn't consider such factors and is happy to terminate sessions without worrying about the effect, then you have a problem. You'll just have to grin and bear it and hope that that there's some form of karma. 


It may help to read a DBA's blog post about a hosting provider admin deleting some of his log files . I love the comments that "anything with 'log' in the name is by convention disposable but useful for diagnosis" and "Log files do build up and unless there is meaningful data in them you can remove them."