ThatJeffSmith

What the SQL? (History)

Tell Others About This Story:

Oracle SQL Developer has 2 client-side logging mechanisms for capturing SQL that is executed:

  1. SQL History
  2. Statements
I tend to keep both panels hidden or closed until I need them. The recording happens whether they are visible or not on the desktop.

I tend to keep both panels hidden or closed until I need them. The recording happens whether they are visible or not on the desktop.

SQL History

This is the SQL that you have executed in a SQL Worksheet. It’s also any scripts you have executed in a SQL Worksheet. Ran something the other day and forgot to save it to your script? No worries, pull it up in the SQL History.

Mouse hover over a script item, and you'll see that I'm not fibbing about entire scripts going into your history...

Mouse hover over a script item, and you’ll see that I’m not fibbing about entire scripts going into your history…

It’s limited by the size defined in the preferences. 100 is the default. I recommend you bump that to 500 or 1,000.

Don't go crazy - each item is saved in an XML file. If you have a really, really big number here, you're asking your machine to carry a big load and you'll notice a longer startup time.

Don’t go crazy – each item is saved in an XML file. If you have a really, really big number here, you’re asking your machine to carry a big load and you’ll notice a longer startup time.

Statements

Accessible when you open the Log panel. This is EVERY statement that goes across the JDBC driver connection to the database. That’s things that you run explicitly (like a query in a worksheet) and everything SQL Developer runs because you implicitly asked for it to (like opening a table editor.)

This feature is available in version 4.1 and higher. It’s a great reason to upgrade if you haven’t already. I use it to help debug privilege issues with customers. You can use it to figure out how we do things and how the database works. Or maybe you’re curious how we get the stats and plan when using AutoTrace?

Things common to both features

You can filter the list. You can sort the lists. You can see how much time was spent executing the item. You can open the panels from the View menu. You can dock them, anywhere.

Command Line History

The equivalent of the SQL History feature is also in SQLcl.

Running the history command with the time argument will show the execution time with each executable statement.

Running the history command with the time argument will show the execution time with each executable statement.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Similar Posts by Content Area: , , , ,