invalid object

What are invalid objects in the database?

Some types of schema objects reference other objects. For example, a view contains a query that references tables or other views, and a PL/SQL subprogram might invoke other subprograms and might use static SQL to reference tables or views. An object that references another object is called a dependent object, and an object being referenced is a referenced object. These references are established at compile time, and if the compiler cannot resolve them, the dependent object being compiled is marked invalid object.

An invalid dependent object must be recompiled against the new definition of a referenced object before the dependent object can be used. Recompilation occurs automatically when the invalid dependent object is referenced.

Does invalid objects affects performance?

Object invalidation affects applications in two ways. First, an invalid object must be revalidated before it can be used by an application. Re-validation adds latency to application execution. If the number of invalid objects is large, the added latency on the first execution can be significant. Second, invalidation of a procedure, function or package can cause exceptions in other sessions concurrently executing the procedure, function or package. If a patch is applied when the application is in use in a different session, the session executing the application notices that an object in use has been invalidated and raises one of the following 4 exceptions: ORA-4061, ORA-4064, ORA-4065 or ORA-4068.

DEMO:

Create one table, one invalid view and one valid view:

SQL> create table first_table ( col1 number, col2 number);

Table created.

SQL> create force view first_view as select col3 from first_table;

Warning: View created with compilation errors.

SQL> create view second_view as select col2 from first_table;

View created.

Check Status:

SQL> select object_name, object_type,status from user_objects;

OBJECT_NAME OBJECT_TYPE STATUS

-------------------- ------------------- -------

FIRST_TABLE TABLE VALID

FIRST_VIEW VIEW INVALID

SECOND_VIEW VIEW VALID

SQL> alter table FIRST_TABLE add (col3 number);

Table altered.

RECOMPILING NOW,

SQL> alter view FIRST_VIEW compile;

SQL> select object_name, object_type,status from user_objects;

OBJECT_NAME OBJECT_TYPE STATUS

-------------------- ------------------- -------

FIRST_TABLE TABLE VALID

FIRST_VIEW VIEW VALID

How to recompile large number of invalid objects?

There will be occasions when you are faced with the need to recompile hundreds or thousands of invalid objects. Typically, this occurs after an upgrade to an application, or perhaps after applying patches. Rather than recompiling them individually, use the supplied utility script. On Unix,

SQL> @?/rdbms/admin/utlrp.sql

How to recompile a pakage?

ALTER PACKAGE pkg1 COMPILE REUSE SETTINGS;