How to recompile invalid schema objects

To check invalid objects in a schema run the following query on SQL*PLUS:

SQL > SELECT * FROM USER_OBJECTS WHERE STATUS = ‘INVALID’;

And to recompile invalid objects in a schema use DBMS_UTILITY.compile_schema utility:

SQL > EXEC DBMS_UTILITY.compile_schema(schema => ‘SCHEMA_NAME’, compile_all => false);

where schema_name is the name of the schema where the database objects exist.

We can also use the UTL_RECOMP package to recompile invalid objects at schema and database level.

----At  schema level
SQL > EXEC UTL_RECOMP.recomp_serial('Mereba');
SQL > EXEC UTL_RECOMP.recomp_parallel(4, 'Mereba');
 ----Database level.
SQL> EXEC UTL_RECOMP.recomp_serial();
SQL> EXEC UTL_RECOMP.recomp_parallel(4);