I went to link to this post earlier today, and was shocked to discover I hadn’t actually written it yet.
What’s the opposite of ‘bazinga!’ ?
SQL Developer stores every query AND script you execute in a worksheet.
A query being a single statement, and a script being one or more queries and/or anonymous blocks.
A query is stored as a single item.
A ‘script’ is stored as a single item.
By DEFAULT, we store the last 100 executed items.
When query 101 is executed, the least recently executed item will ‘age out’ of the list.
The Preference
We do NOT save queries which fail to run due to errors. This saves your precious history from being aged out by bogus queries.
Do NOT set your SQL History limit to ‘30,000’ and then complain that it takes SQLDev 10+ minutes to startup. #TrueStory. Each item is saved in a separate XML file. Each file is read into memory at startup time.
Your Query History is physically stored here – accessible to ALL of your SQL Developer local installs! So there is ONE SQL History stored on your computer, not one for each version of SQL Developer on your machine.
Upgrade SQLDev, your history will still be there.
Accessing the SQL History List
F8 or View > SQL History.
You get a panel.
Items can be sorted and filtered. What’s shown on the panel is what will be available for recall, whether you use the mouse or keyboard.
Note that we store the last time executed, total times executed, and where you executed it.
So you can see it. How do you get it to the SQL Worksheet?
That’s the BORING way to access the history.
The more useful way is via the keyboard. I don’t even open the SQL History panel most days. What I want is usually something I ran in the last few moments. If I have to dig deep, then F8 + Search is my new best friend.
You can change the SQL History keyboard actions in the Preferences.
SQL History in SQLcl
So in the command line edition of SQL Developer, we also have a SQL History. Default is also set to 100 items – it’s not configurable, YET. It is also storing invalid statements…today.
We’ll release SQLcl with ability to set these behaviors.
Recall is similar via keyboard. Just up or down arrow at the prompt.
You can also see the entire list with ‘HISTORY’
Do ‘HISTORY #’ to put that item in the buffer to be executed.
SQLcl and SQL Developer maintain SEPARATE histories. You won’t see queries you ran in SQLcl showing up in SQL Developer and vice versa.
5 Comments
Hi Jeff,
thank you for the article.
Is there a possibility to filter on the connection column in SQL history? Filter seems only search in the SQL column.
I use a history of 500 items and often need to find a statement executed a week ago on a certain connection. It would help to restrict search on one connection.
Thank you!
Kind regards
Stefan
No, but you can sort by connection name, and then do your search…
A tip that I use…
I take SQL History one step further. I leave the SQL History limit relatively low (e.g. 500). This allows me to find a decent amount of past history directly in the tool, but doesn’t delay load time of SQL Developer unnecessarily.
But on both my Mac and previously my PC, I have a batch script that runs 1x/day. The script goes to the SQL History folder and copies the .xml files to a constant target directory under my documents folder.
Now – over the course of 3 years, I have accumulated a considerable number of *.xml files (~ 52,000 files @ 370MB).
If you have PC or Mac, these XML files should be indexed. So I can go to my “SQL History Archive” folder and do a simple search and the OS will perform a Full Text Search. This means I can search for key terms and they don’t have to be an exact match to the strings in the XML file.
With this method, I can very easily recall a script that I last used over 1 year ago. I find myself going back to this folder just about every day.
For the PC, I used a .bat file that I scheduled via Task Scheduler. For Mac, I used a “Calendar Alarm” Automator task.
Thanks,
J
if you have a decent machine, a few thousand entries shouldn’t be a problem – of course if they are – your solution is very nice.
Is there a secret to getting the column widths to remain sticky from session to session? I browse the History every day, and each morning I get to drag the columns around to fit ‘just right’ in the lower part of the main panel. It seems like the column widths presume that they target a very narrow panel.