ORA-3114 due to firewall inactivity cut-off => getting forced to kill PSD instance

Bernhard S

Bernhard S

Member³
Our firewalls have been configured to cut-off sessions after a certain time of inactivity. In an open PL/SQL Developer (PSD) session I get an ORA-3114 then. When I try to disconnect my session I get a sandclock forever and I'm finally forced to kill the PSD instance.

How can I best deal with that issue, without having to go through the kill and recover cycle?
Could PSD be enhanced to send a dummy query like "select 1 from dual" every defined time for each session in order to keep the firewall from closing the session?
In any case PSD should at least be able to deal with this scenario more smoothly. A disconnect/reconnect should do, one would expect, but currently doesn't work.
 
You can enable the "Check connection" option (Tools > Preferences > Oracle / Connection). Now PL/SQL Developer will check once per minute if the session is still okay, which can also act as a keep-alive for connections that might otherwise time out.
 
This setting slightly reduces the problem, but I still do get ORA-3114, long running sandclocks, and finally getting forced to kill the PSD instance. Due to the awful Copy and Paste Problem in 32-bit PL/SQL Developer I currently only use 64-bit PL/SQL Developer, but I assume this issue is not related to the 64-bit version.
The problem gets especially bad when working from home office, which means in additon to running into the firewall timeouts my connections somehow are also affected by the VPN connection.

Even if ORA-3114 or other connection issues are hitting the PSD connections it shouldn't take PSD too long before figuring that out and just reconnect then automatically without crashing or showing endless sandclocks.
 
What happens currently when "Check connection" options is enabled?
I would suggest optionally to combine this option with a configurable, cyclic dummy query like "select 1 from dual" in order to increase chances and options that connections won't end up with a ORA-3114, which seems to be hard to recover from currently.
This feature should be quite easy to implement while increasing happiness of currently affected users immensely.
 
I'm just waiting now again for 10 min for normal closing of an emtpy session which by accident I left open over lunch time. Shouldn't do that with PL/SQL Developer as it doesn't deal well with closing firewalls at all! I have no idea what PSD is doing while showing the ever circling mouse cursor. Forced again to kill the PSD application altogether. Please teach PSD to handle such issues quickly and smoothly as most other applications seem to be able to. What is so hard about figuring out that a connection is dead and asking the user then reconnect or manually do so?
 
I would suggest to play around with Oracle client SQL*Net settings and OS network setting.
Also try to run with DebugSQL option - this may provide some clue.
 
You must log in or register to reply here.
Back
Top