Using DEBUG Mode in Oracle SQL Developer to Log SQL
- by thatjeffsmith
Curious how we’re getting the data you see in SQL Developer when you click on something? While many of the dialogs provide a ‘SQL’ panel that shows you the SQL ABOUT to be generated, I’d rather see the SQL AS it’s executed.
True, you could set a TRACE or fire up a Monitor Sessions report, but both of those solutions leave me hungry for more.
Did you know that SQL Developer has a ‘debug’ mode? It slows the tool down a bit and spits out a lot of information you don’t care about, but it ALSO shows you ALL the SQL that is sent to the database, as you click around the tool!
See ALL the SQL that SQL Developer sends to the database on your behalf
Enable DEBUG Mode
When you see the splash screen as SQL Developer fires up, frantically hit Up, Up, Down, Down, Left, Right, Left, Right, B, A, SELECT, Start.
Wait, wrong game.
No, all you need to do is go to your SQL Developer directory and navigate down to the ‘bin’ directory. In that directory, find the ‘sqldeveloper.conf’ file.
Install Directory - sqldeveloper - bin - sqldeveloper.conf
Open it with a text editor.
Find this line
IncludeConfFile sqldeveloper-nondebug.conf
And replace it with this line
IncludeConfFile sqldeveloper-debug.conf
Save the file.
Start up SQL Developer.
Observe the Logging Page – Log Panel for the SQL
There’s going to be more than just SQL here. You’ll actually see a LOT of other information. If you’re having general problems with the tool and you want to see the nitty-gritty of what’s going on, then this is a good place to satisfy your curiosity and might help us diagnose your issue if you post to the forums or open a ticket with My Oracle Support.
You’ll find ‘INFO’ entries that look a little something like this -
This is the query used to populate your Tables list in the connection tree.
You can double-click on the sql text and get a pop-up window that’s much easier to read.
See all that typing we’re saving you?
I don’t recommend running in DEBUG mode all the time. Capturing this information and displaying it is more expensive than not doing so. And it provides a lot of information you don’t normally need to see. But when you DO want to know what’s going on and why, this is an excellent way of getting that information.
When you’re ready to go back to ‘normal’ mode, just close SQL Developer, go back to your .conf file, and add the ‘nondebug’ bit back.
