Wednesday, July 15, 2009

Generate 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';

select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
from dba_objects where status = 'INVALID'
order by owner, object_name;

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;

select * from dba_errors;

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