When I’m doing my ‘Tips and Tricks’ show, there is at least one trick that is guaranteed to get the audience’s attention:
I’m in an editor – INSERT MAGIC – wham! I’m cycling through the SQL History list, no need to use the mouse.
Here’s a set of keyboard shortcuts I want you to memorize RIGHT NOW:
Ctrl+Down — Next
Ctrl+Up — Previous
The concept of ‘Next’ and ‘Previous’ will depend on the sort order defined in the View – SQL History (F8) dialog. I have mine sorted to Timestamp DESC, so that when I go to ‘Next’, I’m going backwards in time. If I want the query I executed 5 iterations ago, that’s CTRL+Down+Down+Down+Down.
Now before you try this, keep in mind this will CLOBBER your workspace.
So once you get the hang of Down and Up, thrown in a SHIFT to your Ctrl+ keystroke. This will APPEND the SQL to the worksheet instead of clobbering the text.
UNIX command-line fiends already should know this trick, using the Up and Down arrow keys to recall previous commands.
Want to learn more about keyboard shortcuts?
Of course you do! I have a cheat-sheet for you here.
I am not seeing my sql history window any more. Even if I go to view sql history I do not see the sql history window.
But I do see the sql history files under
try using the Window – Reset Windows to Factory Settings menu item.
Double click better than any shortcuts
From SQL history, when I Double-clicked on a query, it used to APPEND to the Editor , this was in SQL Dev 3.1
Same as these 2 does–
ctrl+Shift+UpArrow OR ctrl+Shift+DownArrow
BUT, now I have Sql 4.1,
The double-click does NOT append to the editor… instead it clears off the editor and loads the query!!! its making my day crazy. Any help?
You’re not crazy…it does a replace for me to.
But I’ve never used the UI this way before, I tend to use the kb…I can’t say what the tool did in 2011, my memory won’t allow for that. It’s very possible we changed that. UNDO Ctrl/Cmd+Z should rescue any code you accidentally clobbered though.
It would be nice to have a key shortcut to the SEARCH field in the SQL History view, maybe the F8 key itself could do the trick.
Something like when you press F8, the SQL History window pops up and the focus is on the search field, so you can just type anything you want to retrieve from history, and just hit ENTER to retrieve it to the worksheet
F8, then hit the TAB key 3x, and you’re good to go.
So the SQL Developer’s history function makes more victims. I’ve just lost a long script because I’ve pressed too many CTRL-Down/Up (eclipse habits) so the Undo was useless.
There are two things that should have been different with regards to how this feature was implemented and that can be corrected in a future release:
1. The workspace (that can contain a not-run-yet script) should be retrievable using CTRL+Down/Up. Please see the bash history function (Up/Down keys).
2. The default number of Undo should be greater. There is no reason to have such a low default.
At least the second one is feasible to be implemented in the next release.
The people who posted here are the proof that this function is error prone and that a correction is required, because it’s a good application.
The Undo level is set to 50.
50 would have been a good default in 1990.
Please take a look at bash history feature. Thank you.
I know this is an old thread…
Unfortunately I cannot share your enthusiasm for this particular keyboard shortcut….
I was unaware the effect of Ctrl Up and Ctrl Down until I “clobbered my workspace” with no kind of warnings whatsoever. I didn’t immediately notice that I’d at some point replaced my “masterpiece”(!) with something from history. It was some days later long after I’d “saved” my (now clobbered) work that I discovered the terrible effect of Ctrl Up and Ctrl Down, and I was too late to retrieve the old code from history either (another normally really good feature) because for some reason, the history did not go back beyond the point where Ctrl Up or Ctrl Down had silently deleted my work.
The history feature is great. The trouble is that this is Ctrl Up and Ctrl Down are a common cursor moving shortcut in many editors and applications (Word: start/end para, Excel Start/End of block, and so on(, and I could not believe it had been turned into something potentially destructive here (yes I know we have undo, but that’s only if you realise you need to undo!) .
So first thing for me to do was go delete those shortcut mappings. I use many applications and “muscle memory” makes it far too dangerous.
It feels similar to if you’d mapped Ctrl+S to an “Exit without saving” function.
If we changed it now, I’d probably upset the 1.5M users that don’t use that kb shortcut in the other editors you mentioned. So yeah, I can see why it might not be the most awesome shortcut, but you can change them. You can also increase the number of UNDOs to persist, the default is 50.
Worst two shortcuts in SQL Developer, best SQL tool. I can’t count how many time I lost new queries (not yet executed) due to this ‘feature’. Why not turn it off by default and let less than 10% of the 1.5M users who probably really need it to turn it on manually in preferences? You probably already upset much more % of users than you thought… but you can change it. Please remove this 2 shortcuts, don’t hang on annoying features. Thanks a lot.
I’ve heard this from 2 or 3 users. Maybe it could be better. What would you suggest? What have you re-mapped it to? And how can I train everyone else to learn the new shortcuts?
You don’t lose the query by the way, you just use Undo (Ctrl+Z) to get it back.
I would propose to unmap both and leave it. I don’t think you need to train others, showing note about it where to find keyboard mappings should be enough. I’ve cleared it and didn’t remap it as I’m fine with calling history window with F8. There (when history window is opened for 1st time after install) could be one time popup/notification about history shortcuts mapping.
Maybe I was just annoyed yesterday because I forgot on Ctrl+Z and pressed Ctrl+Up and Down multiple times and could not find what I was showing to someone on other PC (default SQLDev settings). Or there may be probably more important features to cover that this… Have a good weekend 🙂
My SQL Developer is not showing SQL History. previously it worked fine till 30-07-2016.
best case scenario, the desktop just got messed up
Window > Reset Windows to Factory Settings
worst case scenario, the history xml index file got corrupted and you’ll need to ‘repair’ it or nuke it and start from scratch
Try the best case first and we’ll go from there
Thanks a lot…
However, I found another workaround…
#1 – go to C:\Users\\AppData\Roaming\SQL Developer\SqlHistory folder on my windows machine
#2 – There were .xml files till 30-JUL-2017
#3 – I have closed the SQLDeveloper and deleted all the .xml files dated 30-JUL-2016, (considering there is some corrupt .xml files).
#4 – Restarted SQLDeveloper and now I am able see the SQL History and all Shortcuts mentioned above are working fine now
Thank you, Umesh – this worked for me!
How to find the update query from the history of SQL developer. In case if it is deleted intentionally, it is it possible to retrieve?
What update query? The update you ran in a worksheet, or the update that runs when you change a record in a table editor?
This is easily the most annoying and potentially destructive “feature” in SQL Developer. I bump into it on a regular basis, and end up doing a lot of ctrl-Z ing to back out. Because you never press ctrl-down just once, it’s always somewhere between 7 and 33 times.
Anyhow, the answer is simple, you really can disable it! Go into Preferences / Accelerators and scroll down to:
SQL History: Append from Next and then click Remove! Then do the same with SQL History: Append from Previous, SQL History: Replace with Next & SQL History: Replace with Previous.
And that’s it. Now your editor will behave like every other windows text editor, and you will never get caught out again!
Yup, what some people love, others hate.
Of course, if you just wanted every other windows text editor, that’s where you’d be?
I just noticed ctrl+down doesn’t do anything in Notepad++, so I’m not sure why you’re pressing it 33 times in the first place?
Well… Ctrl+Up and Ctrl+Down DO do something in notepad++.
It’s admittedly subtle, but it moves the scroll bar without changing the selection (unless the selection moves off the visible screen)
Ctrl+Up and Ctrl+Down is a common mapping in many applications. The mapping isn’t always the same but in all cases I’ve seen it has something to do with cursor movement. To map it to something unrelated to moving the cursor, with potentially destructive consequences… hmmm… now let me think on that a moment and see if I can imagine why some people might hate it…
I’ll second that – I hit this problem regularly.
Not anymore – now you know the keyboard shortcuts can be reassigned.
awesome, I’ve been looking for years on how to turn this annoying feature off!!
Was hitting the same problem on a Mac. I just wanted to navigate within my SQL file, and hitting Command+Down/Command+Up would erase everything I had written. On Mac, I went to Preferences > Shortcut Keys and in the search box type “History”. Then simply delete the shortcut, and Command+Up/Command+Down will get the default behaviour on the OS again.
Great, you saved my ass with this trick! got back a query I had done weeks prior and forgot to save.
Thank you for a great article, I’ve been looking for years for the ability to scroll SQL history.
It seems to me, the failed commands don’t go into SQL History. Is there a way to make failed SQL commands to go into SQL History?
Thank you in advance.
if we kept up with invalid statements, you’d quickly exhaust your sql limit though…
True. Here is my scenario, I am working on a SELECT statement that has an error, I ran it 3 times and it failed 3 times. I am on the 3rd iteration and it just occurred to me the 1st iteration is what I need. There is no way to retrieve the 1st (failed) iteration thus do no try to develop with errors in SQL Developer. I would like to have at least the option of keeping the failed SQL in SQL History.
yeah, I can see that
when i’m working on a new query, i keep each iteration of it in my worksheet, and if it’s REALLY important, I use a file, and save it after each change – then i can take advantage of the automatic file history feature
We follow the same strategy. I just keep everything in a file and constantly switch between SQL Developer and my file editor… but you know how it is, sometimes I just get too excited or I am in too much of a hurry and then I loose some of my work. It is also very time consuming to constantly switch between SQL Developer and the file editor. The old non-SQL Developer SQL*Worksheet saved both successful and failed SQLs in SQL History and I would like to have this option in SQL Developer.
Jeff, your web site is the greatest for SQL Developer! Many thanks.
I just discovered this feature by accident. So now I’ve completely lost the query that I was working on at the time.
I think you really need to save the current workspace as a version in the history before replacing it with the previously run statement.
It’s not lost! Just undo, ctrl(cmd)+Z will bring your text back.
Thanks Jeff. Crisis averted!
I’m on 220.127.116.11. I used to be able to double click on query from SQL History and it would append the query into current Worksheet, but not in 18.104.22.168
Is there a way to control that behavior ?
That’s intentional. There’s no way to force an append over a replace, but you can always drag and drop or use ctrl+shift+Up/Dn to do an append vs a replace.
Thanks for reply, but it doesn’t make sense why you would pick this as default. Was it the other way around in 3.x?
So it doesn’t clutter up the worksheet. Yes, it was that way in v3, and we didn’t like it. Hence the change.
Can we make it configurable in next releases?
Jeff, my version of SQL Developer apparently shipped with the following. After trying your suggestion and finding that it mysteriously apparently did nothing, I noticed the note at the bottom of the Shortcut Keys window – “Conflicting shortcuts will be removed if the key combination is reassigned.” I decided to sort the table by Shortcut, and sure enough:
Code Editor Scroll Line Down Ctrl-Down
Other SQL History: Replace with Next Ctrl-Down
Code Editor Scroll Line Up Ctrl-Up
Other SQL History: Replace with Previous Ctrl-Up
I tried to reassign the Code Editor ones to Alt- combinations, but counterintuitively, the tool ADDS a rule rather than replacing the existing. So I then deleted original Code Editor shortcuts.
After that, the Code Editor keys worked, but the Ctrl-Up and Down still do nothing if the the cursor is in the worksheet. If I’m in history, it simply scrolls through the list. Ideas?
Jeff, disregard my question. I was running an old version of SQL Developer. I upgraded and the Ctrl-Up and Ctrl-Down work as described.
As you said the the default limit for SQL history is 100. I m not getting the queries that i run yesterday. There are queries available from 31 July 2014 but in the list there only 20 queries. there are only 4 items in the list that were run yesterday.
Also i have not cleared the Sql history.
How can i get those missing queries or only of yesterdays missing queries.
Weird. So you have the sql history panel open – as you run queries, do you see them being added?
If not, it sounds like the file is bad, and you might need to delete it and force SQL Dev to build you a new one from scratch.
Before that, make sure you don’t have a filter enabled on your SQL History panel.
This worked, thank you!
Where does the SQL history get stored in the file system? I’m on Win7 and am using SQL Developer v3.2
Application Data directory, Roaming Profiles, sqldeveloper folder – if you’re on Windows.
Otherwise in your $HOME/.sqldeveloper folder
This directory doesn’t seem to work or exist in Windows 7, I just have a stand alone SQL developer folder that I have the .exe file in.
Check again, try C:\Users\YOU\AppData\Roaming\sqldeveloper
Is there a way to have the SQL History store more queries?
It looks like it defaults to hold the last 100 queries executed.
There sure is, Jeff!
Tools > Preferences > Database > Worksheet > SQL History Limit
Don’t go crazy, but a few hundred or even a thousand is probably OK.
Awesome! Thanks! I will add this to my list of things to do when setting up an user.
I fixed my problem by removing the shortcut key definitions for CTRL+Arrow.
Thanks for the posts 🙂
How on earth can I disable this ‘feature’?
There is nothing quite like seeing a page and a half long SQL statement disappearing into the void when one accidentally hits CTRL+Arrow
Sorry, but glad you were able to figure it out. Undo/Ctrl+Z will save you if you happen to clobber your worksheet text again.
Thank you Jeff, I will certainly remember this tip.
I have to say that SQL Developer improved by leaps and bounds in the last couple of years.
The brand name Oracle IDE’s out there should start taking notes.
great webinar yesterday. thanks 4 that
when scolling thru the sql history using the keyborad, my sql-developer always appends the sql commands in the worksheet.
can you tell me how to change the default behaviour of the sql historiy from append to override?
The defaults are
Of if you want to go the other direction, use the DownArrow instead.
works like charm
thanks 4 enlightening me