This query shows all invalid objects in the Oracle database:

SELECT owner, object_name, object_type, STATUS
FROM dba_objects WHERE STATUS=’INVALID’ ORDER BY owner DESC;

Next is a metaquery that generates all statements to recompile invalid objects:

SELECT ‘ALTER ‘||decode(owner,’PUBLIC’,'PUBLIC’)||’ ‘||
decode(object_type,’PACKAGE BODY’,'PACKAGE’,'TYPE BODY’,'TYPE’,object_type)||’ ‘||
decode(owner,’PUBLIC’,NULL,owner||’.')||’”‘||object_name||’” COMPILE ‘||
decode(object_type,’PACKAGE BODY’,'BODY;’,'TYPE BODY’,'BODY;’,';’)
FROM dba_objects WHERE STATUS=’INVALID’ ORDER BY owner DESC;