DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, September 28, 2016

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

No comments: