Recompile Invalid Objects

Script to find the invalid objects in the database

SQL> Select * From dba_objects where status = 'INVALID';

To Recompile Invalid Objects

Method 1: With access to SYS user

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

Method 2: Without access to SYS user (Cannot validate SYS objects)

SQL> Exec DBMS_UTILITY.compile_schema('SYSTEM');

Method 3: Manual Script

SQL> select 'ALTER ' OBJECT_TYPE ' ' OWNER '.' OBJECT_NAME
' COMPILE;'from dba_objects where status = 'INVALID'and object_type in ('PACKAGE','FUNCTION','PROCEDURE', 'VIEW', 'TRIGGER', 'SYNONYM');

SQL> Select 'ALTER PACKAGE' ' ' OWNER '.' OBJECT_NAME ' COMPILE BODY;' From dba_objects where status = 'INVALID'
And object_type in ('PACKAGE BODY');

SQL> Select 'ALTER MATERIALIZED VIEW' ' ' OWNER '.' OBJECT_NAME ' COMPILE;' From dba_objects where status = 'INVALID'
and object_type in ('UNDEFINED');

Thanks