Validating (Compiling) Invalid Schema Objects

As a database administrator (DBA), you may be asked to revalidate schema objects that have become invalid. Schema objects (such as triggers, procedures, or views) might be invalidated when changes are made to objects on which they depend. For example, if a PL/SQL procedure contains a query on a table and you modify table columns that are referenced in the query, then the PL/SQL procedure becomes invalid. You revalidate schema objects by compiling them.

Note:

It is not always possible to revalidate a schema object that stores program code by compiling it. You may have to take remedial actions first. For example, if a view becomes invalid because a table that it references is deleted, then compiling the view produces an error message that indicates that the table does not exist. You cannot validate the view until you re-create the table.

You can use SQL Developer to run a report that finds invalid schema objects.

To find invalid schema objects:

If the Reports navigator does not appear in SQL Developer, choose the Reports option from the View menu to display the Reports navigator.

The Reports navigator appears.
In the Reports navigator, expand the All Reports node, then expand the Data Dictionary Reports node, then expand the All Objects node, and then click Invalid Objects.

The Select Connection dialog box appears.
In the Select Connection dialog box, select the connection to use, or create a new connection.

If you want the invalid objects report to include information about only the invalid objects in your own schema, use a connection for your own schema.

If you want the invalid objects report to include information about invalid objects throughout the database, use a connection for a privileged user, such as SYS. In this example, the connection chosen is for the SYS user.
Click OK.

The Enter Bind Values dialog box appears.
Click Apply.

The Invalid Objects tab appears in the object pane. This tab lists the invalid objects in your schema or in the database (depending on the connection you specified in the Select Connection dialog box).
When you right-click the row for a particular invalid object on the Invalid Objects tab, the Compile option appears. Select that option to recompile the invalid object.

Description of the illustration sqld_invalid_objs_new.gif

Remember that it is not always possible to make an object valid by recompiling it. See the Note at the beginning of this section.