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
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.
I want to fix an entire schema’s worth of broken stuff!
Right-click on your connection.
Set ‘ALL OBJECTS’ to ‘false’ – that will only recompile invalid objects in that schema.
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…
5 Comments
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
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.
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)
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
You can query the data dictionary, or you can load up this XML extension I put together to show the errors in the View editor.