In case you’re curious, I’m calling yesterday ‘Day 0’ as it was spent doing trivial things like getting email setup. Today I feel like was closer to a real day of work. I spent a few hours this afternoon going over some request from the users. One stuck out pretty prominently –
‘Keep-alive functionality’
The basic gist is at some point your connection to the database is terminated due to an inactive connection. This can be controlled by the DBA via a PROFILE and the IDLE_TIME flag – go read the DOCs
“IDLE_TIME Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.”
Some folks like to login in on Monday morning and logout on Friday afternoon. For various reasons, this is not smiled upon by the admins. To control this, a PROFILE can be established which will go and drop all inactive sessions after a certain time period has elapsed.
What users would like to see is their application (in this case SQL Developer) run a SELECT * FROM DUAL or BEGIN DBMS_OUTPUT.PUT_LINE(”); END; / request periodically. This would keep their connection ACTIVE for as long as their application is running.
Please believe me, I get it, I really do. And even if I didn’t at first, after the 1800th person asked me about it, I finally began to realize that this can be a pain. But.
I’m of the opinion that implementing something like this would not serve our users. I think where we can reach a happy middle ground is to have a really well implemented ‘RECONNECT’ feature which would re-establish any dropped session – whether that be due to someone yanking the ethernet plug from your machine, the database crashing, or a PROFILE limit being enforced.
Eventually this would just be ‘automagic’, but for now just having a button to go resurrect the session will be a big improvement.
Maybe I’m off my rocker? What do you think? DBAs, I’d love to get your feedback here too.
26 Comments
If the goal of not reconnecting is to help track down where in the infrastructure the disconnection happened, then maybe sqldeveloper could help zero in on this by saying what it is not. If it was not the database profile idle timeout, or SQLNET.EXPIRE_TIME not set, or anything that sqldeveloper can verify, then maybe providing this information to the DBA so he can go to the network guys and help to figure this thing out.
We don’t know why our connection is no longer there, only that is is, no longer there.
Did this die ?
New to Sql Developer, which is otherwise a neat environment but this connection issue is horrendous.
and it’s not just about the disconnects.
as far as i can tell, the only way to reconnect is this:
1. find the right connection in connections pane
2. right click > reconnect
3. UI Freezes for a few seconds
4. a dialog pops up informing me about re-connection
5. i have to use the mouse to get rid of this popup (enter does not work)
6. multiply this * connections you have to reopen
this is insane. please tell me there at least is a better way to re-connect
In 4.2 we silently reconnect your connections if we can.
Otherwise, it’s about the same. #5 sounds like a bug to me.
Why are your connections so unstable – are they unstable (VPN), or are they being dropped due to inactivity?
Hi Jeff,
I know this post is very old, but would like to know if any of these ideas was implemented on sqldeveloper. ?
people have written extensions that do what they want, but we haven’t done anything in the tool
Jeff,
If your quote: “…after the 1800th person asked me about it, I finally began to realize….” is true and you are receiving a A LOT of feedback asking for this feature, then doesn’t it make sense to actually include this feature?
Other developers I work with use other tools because they are not automatically disconnected.
Please include this functionality, soon.
Probably closer to 12 people. But regardless of the number, generating fake activity to bypass a network or database setting to close inactive sessions/connections isn’t something we’re going to do.
Now, you do have options.
You can use the extension that someone wrote that does exactly what you want.
You can open a report and set the refresh to like 30 seconds.
You can convince your DBA that your accounts need a profile that doesn’t have an idle disconnect, or has a timeout of three days, or whatever else is appropriate.
Can’t convince your DBA? Then why are you trying to enlist Oracle in circumventing your DBA?
>>Then why are you trying to enlist Oracle in circumventing your DBA?
Enlist? Where’s the poster?
Jeff,
I’ve seen Weblogic systems doing “keep alives” all day and all night – usually “select * from dual” or “select count(*) from dual”. Drives me nuts as each W/L server is doing this any time they go idle from the main application.
Be a lot nicer if W/L decided to stop parsing the query every time. I know it’s a soft parse (mostly) after the first one, but come on, can’t W/L keep the statement parsed for reuse?
Profiles are a bad thing too, if your long running query/update/whatever doesn’t hit the network from time to time, the profile terminates it. All that work has to be rolled back as well – affecting performance on the database I should imagine.
Damned if you do, damned if you don’t.
Cheers,
Norm.
i am not a developer, but… setting the datasource in application server as JBoss there are JDBC parameters like: valid-connection-checker-class-name
why not use that class to reconnect in dev apps?
using “check-valid-connection-sql” and “SELECT * FROM DUAL” always breaks the performance reports (i still have nightmares about this query…)
I am not a developer either, so I invite you to check out the OTN message boards, our developers monitor that community very closely.
I can tell you that any sort of automatic reconnects that happen under the covers COULD be bad – you want to know a connection has dropped, a transaction has rolled back, etc.
I feel compelled to point out the MOS “Your session has been idle for 1 hour. To continue using My Oracle Support, click continue. To end your session, click sign out.” has caused some complaints.
I can’t help but think they have login storms every hour, kicking people trying to log in to forums to a non-working server.
Hey, if we can’t know, our brains are hard-wired to fill in the blanks.
Just a guess, but idle sessions are probably kicked for security purposes. I can dig into it deeper if you’re morbidly curious.
My oracle support offers to re-initialize your session even if you’re actively using it, it happens after 6 hours. However, re-initializing and re-logging in only results in it telling you the session has died, if you try to refresh an SR or anything, so you are forced daily to close all open tabs and re-log in from the beginning.
Gary, Jeff,
maybe you both can both flip by 180° and try to understand the main problem to solve it, instead of just fighting each other?
First of all, let’s sort out network timeouts. these are implemented on active network devices (mostly firewalls, sometimes also routers) to clear up their open access lists. To ‘solve’ this issue, SQLNET.EXPIRE_TIME is sufficient.
Now to your real issue: both DBAs and Developers want to avoid (unnecessary) work. Unfortunately they are see the measurement of ‘necessity’ quite different:
For a Developer it seems to be the worst thing on earth to hit a ‘reconnect’ button. It’s such a burden that this imaginary developer will even search the internet for tools to overcome that, decompose these and re-implement it, in case it’s tool does not provide this functionality out of the box.
For a DBA every resource (in his system) not utilized to ‘do work’ is a kind of dissipation. This makes the DBA running for any method to reduce this avoidable work, including additional triggers, profiles, scripts&reports, etc.
Now back to my request: If you can work together, maybe you can elaborate a setup where you can save both resources: developers time and DBAs DBs with minimal deduction on both sides?
Automatic reconnect is the end-goal. It might just take awhile to get there.
I’m going to try another tack to convince you.
If it isn’t provided as native functionality, it can be provided as an extension. And you can already get a Keep Alive extension by downloading a JAR from someone’s web site and putting it into the exentsions directory.
A really diligent developer will unzip the JAR and decompile the CLASS to check what SQL the extension is going to run. Most won’t, with the effect that they’ll run some hidden bit of SQL from a ‘bloke down the pub’ against the database on a regular basis.
If the thought of that doesn’t scare the DBA, then they are in the wrong job.
And we wonder why DBAs are paranoid and don’t trust the developers 😉 look, if you want to go that route, then that’s your perogative, and I won’t try to stop you.
I completely understand why you want this feature and know where the pain is coming from, I just don’t agree with this approach. I’m afraid it would be abused and hide infrastructure flaws in your network.
Imagine a dba running a top Sql report and noticing a few hundred Sql dev users running all this nonsense Sql, just to keep their sessions established.
I get asked all the time how to build logon triggers to keep users off a system – I think his might serve to add fuel to that fire.
Last year I was at a place where the network would disconnect after 20 minutes of idle time. The Oracle connection would still be there, possibly in the middle of a long-running job. Of course when the server eventually tried to talk to the client and realised it was a dead connection it would rollback. SQLNET.EXPIRE_TIME was set to ensure they didn’t hang around indefinitely.
The people running the network tried to convince us it was in our own interests to have our work terminated. They eventually consented to up the timeout to two hours, but it still got killed after twenty minutes. I think the truth was they were just clueless as to how it was happening. The subject got raised every couple of months until we just gave up.
By denying the option you are basically saying that developers can’t be trusted to use the functionality responsibly. If you start following that path, then I forsee the product getting split into a “DBA Version” and “Dummies version for people who can’t be trusted to walk and chew gum”.
If it’s a long running job, then by definition it’s not idle, right? Sounds like your problem was network related. If you had an artificial fix in your IDE, you’d still have sqlplus scripts getting dropped, right?
It was idle as far as the network was concerned, so it got terminated. Honestly don’t know enough about Java/JDBC to know whether there’s a workaround for that while a procedure is executing or a big update is running.
When the connection wasn’t actually working usefully, I set up a user defined report with a query (eg select 1 from dual) and set the refresh value. Unfortunately the max there is 120 seconds so it ran more than I wanted plus it grabs focus every time it refreshes which is irritating. A max of 999 seconds (or even 9999 seconds) would be preferable.
As I’m one of those nasty DBAs which want to get all out of the instance whenever possible, I like the easy possibility to reconnect at need. In some companies it might be a small problem, as there often are security rules implemented which forbid storing any password anywhere (and for a reconnect you need valid credentials at all?) This might be solvable by some methods to keep interim tokens after an initial handshake, but such a solution must be implemented in SQL*NET first – so it’s another story 🙂
I like the approach that is taken in SSMS. If you execute a query, or statement, and your session has been disconnected you will receive an error message, but it also triggers the application to reconnect to the database. By doing this you if allows the user to immediately re-execute successfully without having to explicitly reconnect. It seems like a perfect trade-off between the DBAs’ and users’ needs.
Thanks for the feedback Tim. I’m looking forward to seeing where we go with the reconnect stuff. I’ll pretend to not know what SSMS is though 🙂