Dynamic query to generate script for compiling invalids manually


Please use the below dynamic query and get the script to compile your invalids instead of compiling 1 by 1.

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

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

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

select ‘alter TRIGGER “‘||owner||'”.’||object_name||’ compile;’ from dba_objects where status=’INVALID’ and object_type=’TRIGGER’;

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

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

select ‘alter public synonym ‘||object_name||’ compile;’ from dba_objects where status=’INVALID’ and object_type=’SYNONYM’ and owner=’PUBLIC’;

select ‘alter synonym “‘||owner||'”.’||object_name||’ compile;’ from dba_objects where status=’INVALID’ and object_type=’SYNONYM’ and owner not in (‘PUBLIC’); 

  • September 28, 2016 | 21 views
  • Comments