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

30 SQL Developer Posts in 30 Days, Day 23: Invalid Objects

1 Min Read

A forum question today reminded me that I’ve never talked about dealing with your broken Oracle database objects in SQL Developer. So let’s take care of that in today’s post.

Browsing

As your browsing your database, you may notice things aren't valid....
As your browsing your database, you may notice things aren’t valid….

Show me EVERYTHING that is broken

Run the report.

Optionally provide a schema to filter the report, the default is to show the entire database.

This is an interactive report. You can right-click on an object and navigate to it or re-compile it.
This is an interactive report. You can right-click on an object and navigate to it or re-compile it.

I want to fix an entire schema’s worth of broken stuff!

Right-click on your connection.

Mind the options!
Mind the options!

Set ‘ALL OBJECTS’ to ‘false’ – that will only recompile invalid objects in that schema.

Good ole Docs 🙂
Just the broken objects, please.

This dialog will run this code:

BEGIN
    dbms_utility.compile_schema(
        schema           => USER,
        compile_all      => FALSE,
        reuse_settings   => TRUE
    );
END;

Note: this can take quite awhile to run, so you may want to open up a session in SQLcl…

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

7 Comments

  1. steve Reply

    Hi Jeff
    It would be nice to show all the Invalid PLSQL package BODY within a schema. I know you can recompile everything but you currently show the SPEC as cleanly compiled eventhough when you click it, the body has an unrunnable “cross” status

    • thatjeffsmith Post Author

      If a BODY is invalid and it’s not shown in that report, that would be a bug. Are you saying the invalid PL/SQL Package Bodies aren’t included in the report?

  2. Kirill Loifman Reply

    Hi Jeff

    You must implement this functionality in the “Find DB Object” section. This is the most useful place to find invalid objects (in all schemas, in specific schemas, with object type filter, etc.)
    So just add another filter criteria “Invalid Object” or “All Object Status”, etc.
    Most of the commercial products have that.

    Thanks

    — Kirill Loifman

    • thatjeffsmith Post Author

      I must?

      I mean, I can try to fit it in. There’s many other ways of doing this, including writing a custom report to do anything you want, which would include having links to the objects.

  3. Bernard Delmée Reply

    I wish the “schema browser” reflected object status in its icons as well, I use it all the time (and miss the visual feedback)

    Also, when expanding the “packages” node, it’s the status of the package header the icon reflects. It would be neat to a have third icon (or background colour) for bringing to the attention that the package body is invalid (and then the user can expand that specific node)

  4. Nigel Richmond Reply

    Jeff

    I see a bunch of Views reported as Invalid Objects, but when I go to the view I don’t see anything wrong with it. Is there any way to figure out why the tool is seeing certain views as invalid?

    Thanks

Write A Comment