Recompile Invalid Objects in Oracle Database

We usually experience Invalid objects in oracle database after importing database or schema using Datapump Export/Import or after database upgrade.


Execute utlrp.sql to recompile invalid objects in database.

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


1.Check the number of invalid objects remaining. This number should decrease with time.

SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);


2.Check number of objects compiled so far. This number should increase with time.

SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;


3. Verify status of invalid objects.There should be no invalid objects.

select owner, object_name, object_type from dba_invalid_objects order by owner, object_type;