Checking and Compiling Invalid Objects

 
Script used for checking and compiling invalid objects.

— Can be executed via sqlplus or any other client tool
— Format output
set pages 1000
set lines 200
col owner for a20
col object_name for a30
col object_type for a30

— Check for any invalid object, you must have select access on dba_objects
select owner, object_name, object_type from dba_objects where status <> 'VALID';

— Generate script to compile Package body (package body must be compiled first before package can be compiled
select 'alter package ' ||owner||'.'||object_name ||' compile body;' from dba_objects where object_type='PACKAGE BODY' and status <> 'VALID';

— Generate script to compile public synonyms
select 'alter PUBLIC ' || object_type || ' ' || object_name || ' compile;' from dba_objects where status <> 'VALID' and OWNER='PUBLIC' and object_type='SYNONYM';

— Generate script to compile all other remaining objects
select 'alter ' || object_type || ' ' ||owner||'.'||object_name ||' compile;' from dba_objects where status <> 'VALID';

If you need more help with database issues, feel free to send me an email (jimmycdo@yahoo.com) and I'll gladly help.

About Jimbart