SQL Developer 4.1 – Log All the Queries!

thatjeffsmith SQL Developer 25 Comments

Tell Others About This Story:

Oracle SQL Developer is a graphical user interface and integrated development environment for Oracle Database. As you can imagine, a few clicks can results in an awful lot of queries being executed on your behalf.

If you don’t like this fact, you can always use a command-line interface like SQL*Plus or the new sdsql. The only thing that runs there is what you explicitly type..mostly.

Anyways, in version 4.1, you can now see all of the queries going across the ‘JDBC wire’ so to speak.

Open the Log

when something is written to a panel for you to see, it changes color...

when something is written to a panel for you to see, it changes color…

The Statements panel is what we’re looking for.

Do Something in SQL Developer

You could start typing in a worksheet, or click on a table, or launch a report, or…just about anything. In this case I’m going to load the tables in tree.

I get this question ALL THE TIME: Why can’t I see my tables?

So, if you wanna see how we get your tables, now you can – without the effort of doing a trace or digging through v$sql_whatever.

Double-click on the cell to get the easier to read query by itself in the popup box

Double-click on the cell to get the easier to read query by itself in the popup box

So you can see we’re doing a lot more than just SELECT * FROM USER_TABLES here.

The ‘Tricks’

There’s no tricks really. We’re always logging. It’s done at JDBC level – there’s no performance overhead for using this feature. This will capture queries SQL Developer itself is generating, queries the user is executing, and even queries that the underlying framework provided by JDeveloper are using. It will even grab whatever queries are being executed by 3rd party extensions.

There’s no turning it ‘on’ or ‘off’ – it’s just a matter of opening the panel to look at the information or not.

You can clear the panel. If you want to see what the tool is doing, clear the log, and ‘do the thing.’ EZ-PZ.

You can filter. Type whatever text you’re looking for…

Type and hit enter in the search box

Type and hit enter in the search box

We capture the queries and record the sequence so you can step through what’s what.

And we capture the binds, so you can see what values are passed over into your queries.

So, What to Do With This?

I’ll be using it to help answer customers’ questions. Easier than drilling through the source code. I imagine many of you will be using it to see who we’re doing stuff, so you can go do the same stuff. Like, how is SQL Developer grabbing waits in the Instance Viewer? I want to do that same thing in MY report…and now you can.

I think this might be a popular feature. So does this guy.

Tell Others About This Story:

Comments 25

  1. JEFF SMITH,

    Can SQL-Developer log the logic of the SQL execution ? (which condition of the WHERE clause failed ?)
    example:

    select 1
    from Dual
    WHERE
    1 = 1
    AND 1 = 1 + 1
    ;

    Can we know the above sql statement failed at the line number 5 ?

    Thanks & Apologize if the question is not suitable with you.

    PV.

    1. thatjeffsmith Post
      Author

      Nothing really failed…it’s just that your predicate clause 1 = 1+1 will always evaluate to FALSE.

      But, is there a way to see the FALSE’s? Not that I know of, but this is a great question.

      1. YES, thatjeffsmith. I mean so : true | false.

        With complex|analytic SQL statement, I spend much time to debug the logic of SQL statement (comment – decomment line by line to check the condition).

        Appreciate your feedback.

        Thanks & Regards.
        PV.

      2. thatjeffsmith,

        In case you have updated infomation or workaround solution or any advises on this, please update.

        Thanks & Regards.
        PV.

        1. thatjeffsmith Post
          Author

          I was on vacation this past week. Even so, I don’t have any updates for you. You could try asking the AskTom team, they might know if something cool along the lines you’re looking for.

  2. but this statement-logging is not available when you’re using jdbc. not when you checked “Use Oracle Client” right?

      1. thatjeffsmith Post
        Author
  3. Any way to turn this off? No matter what I do, it pops up with every mouse click or scroll. It pushes itself to the front of the “message” pane that actually contains useful information about my compile. Then I have to click on the message pane to see it. About 100 times a day….

    1. thatjeffsmith Post
      Author
      1. There’s no “minimize” once the Message panel has focus. rt-clicking on the “statements” panel only offers “Close” and “Close All” as options. Closing it doesn’t help. It comes right back.

        1. thatjeffsmith Post
          Author
          1. I’m running 4.1.0.19 on Windows 7. There is no minimize button. Only a “down arrow”. I’ve clicked all around, left and right…no “minimize” available in any menu anywhere that I can find (apologies if this is a head-banging moment for you…I’m a Mac user at home and only use Windows at work)

          2. thatjeffsmith Post
            Author
  4. I can confirm that you’ll need to go into Preferences > Database > Advanced, check the “Use OCI/Thick driver” box, uncheck the “Use Oracle Client” box, and restart SQL Developer for the Statements to appear.

  5. I’m having a problem where the Statement Log is not appearing in subsequent runs of SQLDev 4.1. I’ve tried reinstalling the product fresh (except for importing my Connections), then using the product over the course of a day; the Statements Log works as expected.

    Then the next day, the tab isn’t visible in the Messages pane at all! Unfortunately, I can’t find a pattern to re-create this!

    Be interested to see what this bug might be!

    1. thatjeffsmith Post
      Author
      1. Just tried turning Oracle Client off with the Thick checkbox checked, and voila (sp?)! 🙂 The Statements are showing!

    2. Jeff:

      Whoops..should have mentioned that I’m running SQLDev under Windows 7 32-Bit.

      Using a 12c Instant Client for Thin-Client duties to access 11g databases on 64-Bit Win2012 servers. (Thought I saw something whilst Googling which stated that a 12c client was required for this functionality to work.)

  6. Jeff, I’m not seeing this feature in the Linux version of SQL Developer 4.1. I can open the log window, but the only two tabs visible are Messages and Logging Page. There is no Statements tab. Is there a preferences setting I might be missing?

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *