Wednesday, November 13, 2013

script to compile all invalid object on oracle database?

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

select 'alter package man.'||object_name||' compile body;' from dba_objects where owner='man' and status='INVALID' and object_type='PACKAGE BODY';
select 'alter function man.'||object_name||' compile ;' from dba_objects where owner='man' and status='INVALID' and object_type='FUNCTION';
select 'alter procedure man.'||object_name||' compile ;' from dba_objects where owner='man' and status='INVALID' and object_type='PROCEDURE';
select 'alter view stage.'||object_name||' compile;' from dba_objects where owner='stage' and status='INVALID' and object_type='VIEW';
select 'alter procedure stage.'||object_name||' compile ;' from dba_objects where owner='stage' and status='INVALID' and object_type='PROCEDURE';

No comments:

Post a Comment