So you’re minding your own business, doing your work, and you click in your favorite Oracle Database IDE. Do you get what you asked for? No! Instead, you are rewarded for your troubles with this puzzling and frustrating message.
Abort? Try again?
Busy?
The Long Answer
When you connect to a database in SQL Developer you are actually making two connections. We use one connection to run background queries in the the worksheet. This makes the code assist features work.
The other connection is used to do EVERYTHING else. The only exception is when you open an UNSHARED worksheet. It’s called ‘unshared’ because we open another dedicated connection just for that worksheet.
So, if you see this message in SQL Developer, it means your request has timed out while waiting for the main connection to become available.
For example: when you ask to see a table’s data in a table editor, we first ask to lock the JDBC connection so we can run our ‘SELECT * FROM…’ query. If the JDBC comes back and says, sorry we’re busy, we try again. We give up after a certain amount of time and give you the error message.
At that point you get to:
- Try again – ask for the lock to run the query again
- Abort – give up
Now, I realize this explanation has only given you more questions to ask, so let’s go through those now.
But I’m not doing anything, how could the connection be busy
What’s probably happening is the columns page query is still technically executing when you go to click on the ‘Data’ page in a view or table editor. Or, something you’re doing somewhere else in the tool is still using the main connection.
Still don’t think you’re doing anything? Do you have any reports (or Monitor Sessions) on auto-refresh? Or maybe you’ve gotten cute and installed/created a ‘keep alive’ extension to thwart connection-idle disconnects? Oh yeah…
In earlier versions of SQL Developer you might run into this error more frequently because of poor session management – that’s on us. But in version 4, you should VERY rarely see this message.
In fact I’ve been spending the entire morning trying to get this message to pop-up and I failed.
Even if I click ‘Abort’ the data comes back anyway?!?
The dance of the ‘are you busy’ and the ‘yes we are’ is colliding with the timeout perfectly just so that the dialog is triggered as we’re able to run the query for you. If you see this frequently, it means the delay setting is just a hair-low for you.
How do I tell SQL Developer to give it another second or so to try before giving up?
[text][/text]
-Dsqldev.obtainlockwaittime=[X] where x = seconds
[text][/text]
The default is 1 second. If you add this line to your sqldeveloper.conf file in the bin directory, you can tell SQL Developer to give your connection another second or three before it should ‘give up.’
Can’t SQL Developer just ‘be smarter?’
Yes. We’re talking now about having connection speed measured and having the delay period dynamically set on a connection when we can tell that response time is going to ‘suck.’
The Short Answer
Dude, this is a blog. I don’t do short answers here. That’s what Twitter is for 🙂
37 Comments
Regarding “In earlier versions of SQL Developer you might run into this error more frequently because of poor session management – that’s on us. But in version 4, you should VERY rarely see this message”…
I have 4.2 version, but I keep getting this error more frequently. I suspect issue is not fixed completely
It’s possible. Set up a scenario for us, and we can take a look.
Also check out version 17.4, might make things better with no work.
I visit this page as I open a second instance of SQL Developer because my first is locked.
1 – I stupidly run a query that is not optimal and wait 20 minutes before clicking cancel.
2 – Nothing works. I try to reconnect, disconnect, etc. No response from UI even though it shows the menus.
3 – I start a new SQL Developer, copy over my work, reopen new connections, and Ctrl + Alt + Del the old one.
It happens at least daily.
Query cancellation should be TONS better once 4.2 is released – we’re able to do smarter things with the JDBC driver now in that area.
For you, today.
I would do your real query work in an unshared worksheet.
Thanks Jeff! I appreciate the reply and will give that a go.
When user strikes ALT+F4 or just pushes the X button on the top-right edge of the window, the software has only one thing to do that is to close.
A “Connection is busy” message has nothing to do with it, when I ask to close the window it “must” close no matter what it’s currently doing.
Actually I can get the same by just: stopping my Oracle instance, closing my network connection, and so on. Any connection open by SQL Developer will close and allow me to close the window definitely.
It’s just annoying that I must close something different in order to close a SQL Developer window just because it’s unable to do it itself.
Yes, it could be smarter, it’s not yet.
I have a connection that I cannot disconnect (just doesn’t do anything when I hit “Disconnect”. I would expect the “Connect” option to be available after the Disconnect but it isn’t). When I try to shut down SQL Developer I get the error message quoted above. If I “Try Again”, it tries again but then displays the message. If I “Abort”, well I’m back to where I started … and can’t close SQL Developer !! Oh well, Task Manager to the rescue.
I’m using version 4.1.0.19 in Windows8.
all we can do is ‘ask’ for the session to disconnect via the jdbc driver
some customers have better experiences with certain databases if they force a THICK connection using an Oracle (instant) Client
I really think the UI should have the luxury of resetting all connections (such as a force disconnect option) to save the current state of the UI and not leave it to mercy of the db connection handler.
Many times it leaves me no choice than to kill sqldeveloper(and lose my current workspace setting; open scripts, ad-hoc queries written in the worksheets, recent files etc.) and restart from scratch. Of couse, I can also wait for 15-20 minutes to let sqldeveloper become responsive again but you can understand that is not very productive use of a developer’s time.
I understand that discarding connections without properly handling them doesn’t sound very nice from a DBA point of view but I still believe there should be a way to take care of this. Maybe there could be an option like ‘kill session from db and force reset all connections (or a specific connection)’ which would only be available if the user has dba priviliges.
In the current setting, even if you manually kill the session from the db, it still takes a lot time (more than a few minutes) for sqldeveloper to find that out and reset connection.
Thanks,
I agree – when the only option available is to kill SQL Developer from the OS (losing any work saved in the process) something is wrong with SQL Developer. It should allow me to “abandon” the session completely in cases where neither “Try Again” and “Abort” work.
Killing the session from the db, you do you have more than the nuclear/ctrl+alt+delete option.
We’re a client-server app, we play in those boundaries.
You can minimize losing work by doing all of your code dev in files, using SQL History to capture your code between saves, and saving your work before every compile/execute.
I don’t always get enough access to the db at client sites to be able to kill sessions, and the DBAs are not always available for dev support (since Prod is their priority). Of course I always save all my changes before running them – EXCEPT on the odd occasion when I forget (which, naturally, is the time that the session decides to stop responding). I should be able to disconnect a session without having to kill the SQL Developer – what boundary is stopping SQL Developer from abandoning the session?
the JDBC driver
FYI JetBrains DataGrip allows killing sessions with no issues. They use JDBC as well.
What driver are they using.
I get this error pretty much everyday but I dont have to abort the session. The dialogue box appears but then its shows the result set as well.
I am using Oracle SQL Developer version 1.5.0.53 and on a company’s shared network.
Are there any settings that I can modify to stop these messages popping up.
Sorry, but no.
You need to upgrade. Version 1.5.5 is almost 10 years old.
Great blog, Jeff!
I do see this message frequently in a use case that you might not have considered.
– Run a lengthy update (or stored procedure call) while connected to a server through a VPN
– The VPN times out
– The operation may or may not have completed.
– Return to the computer; reestablish VPN
– Try to exit SQL Developer
– SQL developer shows the ‘connection busy’ message.
– This happens sometimes while the VPN remains connected; it’s the long-term operation that causes the issue.
If you do a Reconnect on your session after the VPN comes back, does the error message still appear? After the network goes down, everything after that is going to be ‘fun.’ We try to re-establish the connection…
I’ve tried reconnect but it does not seem to work when SQL Developer is in that state.
if the connection is busy, there’s nothing to do connection-wise, until it’s NOT busy…or startup an unshared worksheet for querying
what is an “unshared worksheet”?
when you are in a SQL Worksheet, and hit the ‘new worksheet’ toolbar button, it opens another worksheet, but on a new, dedicated Oracle connection. The worksheet shares a connection with the reports interface and the object browser/editors. So if a query is busy running on that connection, the worksheet has to wait.
An unshared worksheet never has to wait.
Sorry, whatever was causing the session to hang has stopped causing the problem – so I’ve been unable to reproduce it. It’s happened on/off over the past few months. If/when it happens again I’ll post.
I get this error regularly when trying to run a query that goes across a database link. What’s extremely frustrating is that the only way to recover is to kill the SQL Developer session with Task Manager, then reopen SQL Developer and reopen the scripts I was working on (that the first session didn’t remember).
This popup should have an “Abort” option that REALLY aborts – i.e. just forget that connection, but keep the UI running so I can at least save my work and then make a new connection to the database.
Using version SQL Developer 4.0.2.15 connecting to Oracle 11.2.0.3.
I wonder why we think the connection is busy. Next time try it on an unshared worksheet and let me know what happens. You should be able to cancel the query, and have it do like what you expect, but I’m not sure how the Connection Busy dialog is coming into play at that point.
Just try to insert a unique key that already exists. Its 100% fail rate for me atleast (Windows and v. 4.0.1.14 Build MAIN-14.48). Sure i shouldn’t do that but wtf how cant a company like Oracle handle that.
/peter
Can you tell me if the leading – and the [ ] surrounding the number of seconds is the right syntax?
-Dsqldev.obtainlockwaittime=[X] where x = seconds
is this correct?
Dsqldev.obtainlockwaittime=15
or is this correct?
-Dsqldev.obtainlockwaittime=15
or is this correct?
-Dsqldev.obtainlockwaittime=[15]
Thanks,
Barry
Hi Barry
(I suppose you solved it after 6 years, but maybe interessting for other readers…)
I tried with
-Dsqldev.obtainlockwaittime=2
and
Dsqldev.obtainlockwaittime=2
and got a message “invalid line ….”
With
AddVMOption -Dsqldev.obtainlockwaittime=2
the message disappeared. I hope this setting does what I’m expecting.
I’ve this error a lot!!! Like more then 5 times a day. Really boring.
Have you upgraded to version 4 yet? I’ve not seen this message for more than a year now.
Yeap, I’ve the latest version here. 🙁
How far away/remote is your database?
It’s close, on the company network. And it happens with any bd..
Maybe just use a connection pool?
I mean, what’s the problem of using some more connections if “the connection” is not yet ready?
For instance: “Reports” never use unshared connections. That really frustrates when trying to use “active sessions” report during long sql being executed in the “root” connection.
I use a SYS or 2nd connection to run that screen, that way it’s always available.
Yes, I use the same WA when I know I would need reports.
I just don’t always run reports, thus I run into the issue from time to time when running a report on a ad-hoc basis.
I guess a connection pool for system requests might help a lot.