Got a great question off the Twitter-verse this morning from @ApoloDuvalis
“..in SD (SQL Developer) you cannot use the same connection in a different command window until the busy command is done or aborted.”
I know many of you aren’t familiar with queries that take so long to run that you have to wait on them, but believe me, they are out there! Anytime I need a query that takes more than a few seconds to run, I jump to the data dictionary views and ask for stupid things like this –
SELECT b.data_object_id, d.text FROM all_objects b, all_source d WHERE b.object_name = d.name ORDER BY b.last_ddl_time DESC;
Don’t even bother looking at the plan for that, it’s nasty, and that’s kind of the point really! Anyways, it takes about 30 seconds to run, and I need to do other things while I’m waiting for that to come back.
Extra Bonus Tip!
Use statement delimiters (;) to end your queries, then you can use Ctrl-Enter to auto-execute the query at cursor. If you don’t use statement delimiters, you may have to select the text you want to execute first.
Here is the keyboard shortcut you need to memorize for the Worksheet
Ctrl-Shift-N
‘N’ probably stands for some weird coder jive like ‘Nero’ or ‘Narnia’, but I’m going with ‘New’, as in ‘New Worksheet.’
If you can’t remember this, then you can always pull it up in the SQL Developer Preferences window, on the ‘Shortcut Keys’ page.
Once you hit that key sequence, SQL Developer will fire up another window to that same instance so you can work up your next query to save the world (or build that spreadsheet for your boss.)
Trick: Default New Worksheets as ‘Unshared’
Every worksheet you open will be unshared – minus the default worksheet you get on a connection.
Here’s a quick demo:
10 Comments
You might want to mention that the Ctrl-Shift-N shortcut only works if the focus is on a shared worksheet.
You can’t spawn an unshared worksheet when you’re on another unshared worksheet, correct.
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’m with you, to a point.
We’re not Notepad, we’re a database GUI – and that means we have a connection. And connections have transactions. Let’s say you’re in the middle of updating a 1,000,000 records and you just yank the app away? The db connection has to undo that work. Now, you’re not yanking anything, you’re asking to close.
When you close the tool with open connections, we ask if you want to disconnect/save your work first.
If the connection is busy, that implies it’s doing work…so you’re saying if you close, we should auto-cancel that work. Even asking to cancel a task in the database can take time.
FWIW, I rarely encounter the scenario you’re describing. I’m hoping it’s not something you experience more than a every-once-in-awhile.
Can’t always think about a million records transaction, nothing I would ever do through the SQL Developer GUI or any other one, not even while connected remotely through some VPN.
And however SQL Developer is intended for developers not for average users, and developers are (supposed to be) well informed of what happens when a connection is forcefully closed in the middle of a transaction, and they also (are supposed to) know very well how the (Oracle) DBMS manages such situations.
On the other hand you must consider that sometimes the connection opened by SQL Developer is “actually” doing nothing, that is, it “should be” doing nothing.
A simple select statement must not take ages to be canceled since it does not imply any transaction, thus forcing the connection to close actually produces nothing wrong.
Only today I had to forcefully close my SQL Developer some 20 times due to the connection hanging on that boring “Connection is busy” message.
What I’m saying is that most times that busy connection shouldn’t really be that much busy.
And however SQL Developer is intended for developers not for average users, and developers are (supposed to be) well informed of what happens when a connection is forcefully closed in the middle of a transaction, and they also (are supposed to) know very well how the (Oracle) DBMS manages such situations.
We’re the default interface for the database, so it’s for more than just developers, and even your avg app devs that work on Oracle don’t necessarily understand query/connection management.
On the other hand you must consider that sometimes the connection opened by SQL Developer is “actually” doing nothing, that is, it “should be” doing nothing
Often, yes. Often, no. Check the view > log > statements panel. You’ll see we run MUCH more than just what you type in an editor.
A simple select statement must not take ages to be canceled since it does not imply any transaction
Yeah, but you might be asking for a ton of math/sorts that require TEMP space – space that has to be allocated and let go. Force killing connections isn’t something we do, for many reasons.
Only today I had to forcefully close my SQL Developer some 20 times due to the connection hanging on that boring “Connection is busy” message.
Instead of debating here, let’s work together to figure out what’s happening on your system.
What version of SQL Developer are you using? What version of Oracle? Can you check with another connection to see what the current statement, locks, and waits that might apply to your ‘connection is busy’ connection?
You say you’re over a VPN? If it’s a very bad connection, it’s possible the jdbc layer is cranky and you might want to tune some jvm properties on timeouts so it gives up faster.
But without more info about your scenario, I’m just left with guessing.
Also, this is just a blog. I maintain it to help our users. If you want formal support, you can always open a Service Request with My Oracle Support.
“And however SQL Developer is intended for developers not for average users, and developers are (supposed to be) well informed of what happens when a connection is forcefully closed in the middle of a transaction, and they also (are supposed to) know very well how the (Oracle) DBMS manages such situations.”
My favorite quote from Jonathan Lewis seems appropriate here:
“Most of the code in the Oracle kernel behaves as if application developers understand exactly what they are doing, and know how to use relational databases – this is, of course, a serious design flaw.”
Thanks for your hint 🙂
BTW: Thanks for the hint. I didn’t know about it 🙂
May I suggest to make Ctrl-Shift-N working in a child Worksheet? The combination works from the main/original worksheet. I think it would be handy if we can open another and another and another window without switching to the first one.
Just my .02c