Cleanup invalid objects

#extract DDL for invalid objects
select 'select dbms_metadata.get_ddl('''||replace(OBJECT_TYPE,'PACKAGE BODY','PACKAGE')||''','''||OBJECT_NAME||''','''||OWNER||''') stmt from dual;'
from dba_objects where status = 'INVALID';

# try to compile invalid objects
select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
from dba_objects where status = 'INVALID'
order by owner, object_name;

# compile public synonyms
select 'alter public synonym '||object_name||' compile;'
from dba_objects where status = 'INVALID' and object_type = 'SYNONYM' and owner = 'PUBLIC'
order by owner, object_name;

# list all compile errors
select * from dba_errors;

# drop invalid objects
select 'drop '||object_type||' '||owner||'.'||object_name||';' from dba_objects where status = 'INVALID' order by owner, object_name;

# identify packages with no body
select p.owner, p.object_name from
dba_objects p
where p.object_type = 'PACKAGE'
and not exists (select 1 from dba_objects ph where p.owner = ph.owner and p.object_name = ph.object_name and ph.object_type = 'PACKAGE BODY');