Monday, January 21, 2013

Oracle recompile invalid objects


SQL to select all invalid objects(that have errors or need to be just recompiled because Oracle is lazy):
select
   object_type ,
   owner,
   object_name
from
   dba_objects
where
   status != 'VALID';
 
SQL to grab objects and try to recompile them to fileter out that have Errors and object that just invalidated by Oracle for any internal reason:
select
   'ALTER ' || object_type || ' ' ||
   owner       || '.' ||
   object_name || ' COMPILE;'
from
   dba_objects
where
   status != 'VALID'
   and (object_type='VIEW'
   or object_type='MATERIALIZED VIEW'
   or object_type='TRIGGER'
   or object_type='FUNCTION'
   or object_type='PROCEDURE'
   or object_type='PACKAGE'
);

select
   'ALTER PUBLIC ' || object_type || ' ' ||
   object_name || ' COMPILE;'
from
   dba_objects
where
   status != 'VALID'
   and object_type='SYNONYM'
   and owner='PUBLIC';

select
   'ALTER ' || object_type || ' ' || OWNER || '.' ||
   object_name || ' COMPILE;'
from
   dba_objects
where
   status != 'VALID'
   and object_type='SYNONYM'
   ;

-- SYNONYMs could be compiled only as sysdba , so do it by "sqlplus sys/oraclexe as sysdba"

select
   'ALTER ' || object_type || ' ' ||
   owner       || '.' ||
   object_name || ' COMPILE;'
from
   dba_objects
where
   status != 'VALID'
   and object_type='SYNONYM'
   and owner<>'PUBLIC';
 
select
   'ALTER PACKAGE' || ' ' ||
   owner       || '.' ||
   object_name || ' COMPILE BODY;'
from
   dba_objects
where
   status != 'VALID'
   and object_type='PACKAGE BODY';



No comments:

Post a Comment