Script to compile invalid objects and unusable indexes

 select owner,count(*) from dba_objects where status='INVALID' group by owner order by owner;

 select object_type,count(*) from dba_objects where status='INVALID' group by object_type order by object_type;

select owner,object_name,object_type from dba_objects where status='INVALID' order by object_type;


select 'alter PROCEDURE '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type='PROCEDURE';

select 'alter VIEW '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type='VIEW';

select 'alter TRIGGER '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type='trigger';

select 'alter FUNCTION '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type='FUNCTION';

select 'alter PACKAGE '||owner||'.'||object_name||' compile ;' from dba_objects where status='INVALID' and object_type='PACKAGE';

select 'alter PACKAGE '||owner||'.'||object_name||' compile body;' from dba_objects where status='INVALID' and object_type='PACKAGE BODY';

select 'alter SYNONYM '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type='SYNONYM';

select 'alter PUBLIC SYNONYM '||object_name||' compile;' from dba_objects where status='INVALID' and object_type='SYNONYM' AND OWNER='PUBLIC';


select 'alter index '||owner||'.'||index_name||' rebuild parallel 4 online;' from dba_indexes where status='UNUSABLE';