begin for i in (select distinct owner from dba_objects where status='INVALID') loop dbms_utility.compile_schema(schema=>i.owner); end loop; end;
set serveroutput on declare cmd VARCHAR2(500); begin for i in (select owner, object_type otype, object_name oname from dba_objects where status='INVALID' order by owner, otype) loop if i.otype='PACKAGE BODY' then cmd := 'alter package ' || i.owner || '.' || i.oname || ' compile body;'; ELSE cmd := 'alter ' || i.otype || ' ' || i.owner || '.' || i.oname || ' compile;'; END IF; dbms_output.put_line( cmd ); end loop; end; /