Troubleshoot invalid objects in Oracle

Most of the procedural objects like procedures, functions, triggers, objects refer to data objects, such as table.

-If the code refers to a column, the column must exist or the code will not compile.
-If any of the data objects to which a procedural object refers change after the procedural object has been compiled, then the procedure will be marked INVALID.
-Procedural objects may also be invalid for more mundane reasons: perhaps the
programmer made a simple syntactical mistake.

The same situation can occur with views. When created they may be fine, but they will be invalidated if the tables on which they are based have their definitions changed.

Identifying Invalid Objects
--------------------------------
To identify all of the invalid objects , run the following query,

SQL> select owner,object_name,object_type from dba_objects where
status='INVALID';

How to Handle Invalid Objects:
-------------------------------
1)Compile the invalid objects:
-------------------------------
The first steps is to compile the invalid objects. Though, the first time an invalid object is accessed, Oracle will attempt to compile it automatically, but if the compilation fails, the user will receive an error. Clearly, it is better for the DBA to compile it first; then, if there is an error, he can try to fix it before a user notices.

2)If compilation is ok then there is no problem. But if it fails then check the object and object type. Suppose if the object type is procedure then you can use SHOW ERRORS to identify the the errors.

3)If the object type is view then SHOW ERRORS will not work then try to see the creation script of the view.

SELECT TEXT FROM DBA_VIEWS WHERE VIEW_NAME='YOUR VIEW';

Generally you can use DBA_DEPENDENCIES object and query column REFERENCED_OWNER and REFERENCED_NAME.

4)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

Workaround Example:
---------------------
1)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.

2)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

3)Drop Column col2 from Table and Check Status:
-----------------------------------------------
SQL> alter table first_table drop column col2;
Table altered.

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 INVALID

4)Add column col3 and Check Status:
--------------------------------------
SQL> alter table FIRST_TABLE add (col3 number);
Table altered.

SQL> alter view FIRST_VIEW compile;
View altered.

SQL> select object_name, object_type,status from user_objects;

OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW VALID
SECOND_VIEW VIEW INVALID

5)Troubleshoot Second View:
-----------------------------
SQL> select text from user_views where view_name='SECOND_VIEW';
TEXT
--------------------------------------------------------------------------------
select col2 from first_table

SQL> select REFERENCED_NAME,REFERENCED_TYPE from user_dependencies where name='SECOND_VIEW';

REFERENCED_NAME REFERENCED_TYPE
-------------------- -----------------
COL2 NON-EXISTENT
COL2 NON-EXISTENT
FIRST_TABLE TABLE

So col2 is non-existent or missing.

SQL> alter table FIRST_TABLE add col2 number;
Table altered.
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 INVALID

Interestingly when I add col2 FIRST_VIEW becomes invalid.

And also when deleting a referenced column status of dependent objects become invalid. But when we add the status does not changes.

6)Now compile on second_view or any access will marked the dictionary ok.

SQL> select * from second_view;
no rows selected

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

But First_view become INVALID.

7)Fix Error:
---------------
SQL> alter view FIRST_VIEW compile;

View altered.

SQL> select object_name, object_type,status from user_objects;

OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW VALID
SECOND_VIEW VIEW VALID

SQL> select REFERENCED_NAME,REFERENCED_TYPE from user_dependencies where name='SECOND_VIEW';

REFERENCED_NAME REFERENCED_TYPE
-------------------- -----------------
FIRST_TABLE TABLE