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"
'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