Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

2 Reports You Might Enjoy in Oracle SQL Developer

2 Mins Read

I keep telling people our reporting functionality is probably the most powerful AND overlooked feature in SQL Developer.

Today I’m going to share 2 reports that I frequently forget about, that you may enjoy.

Finding Unindexed Foreign Keys

We all know this should not happen. We all know it eventually happens. Here’s a report to help you quickly find these in your database.

Found under the Quality Assurance section of your database reports.
Found under the Quality Assurance section of your database reports.

You could easily make this a custom report and have a child page that showed a computed DDL statement to build your index, or include an object hyperlink to the ‘offending’ table…

Top SQL – Tune It!

This report requires both the Diagnostic and Tuning Packs be licensed in the databases you want to run it. You can then ask for expensive queries by wait events over the past X seconds. And when you click one, you can see the SQL Tuning Advisor task output for your offending query.

It will prompt you for time in seconds to go back in Active Session History (ASH) .
It will prompt you for time in seconds to go back in Active Session History (ASH) .

And you can find this report here:

Remember: this touches ASH and the SQL Tuning Advisor.
Remember: this touches ASH and the SQL Tuning Advisor.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

5 Comments

  1. Phil Reply

    Hi Jeff, thanks for the article.

    I was wondering where I have a foreign key that references the primary key on another table and that primary key is indexed, is there a need or advantage to create an index for the foreign key?

  2. Azhar Reply

    Hi Jeff,
    I just run that report and found 41 tables without FK indexes. Thanks for sharing.

    Regards,
    Azhar

    • Steve Lyon

      I love the user-defined reports feature. Especially that you can build child/drill-down reports — this is very powerful and helpful. I use them all the time. You can even have child-reports that are PL-SQL based using dbms-output.

      However, UNFORTUNATELY, I haven’t found a way to bypass the 20,000 character output limit. The normal limit is much bigger — but in these user defined reports I haven’t found a way to get by this 20,000 limit.

      Is there a way to do that? (Please Help)

    • thatjeffsmith Post Author

      yes, there’s a way

      add this to your anon block in your plsql report code
      dbms_output.enable(50000); — now your limit is 50,000 characters

Write A Comment