How to prepare script to compile all invalid objects in oracle database

This document describes how to prepare script to compile all invalid objects in the database.

Some times we may get hundreds of invalid objects in the database. We need to compile to fix those objects. We can manually compile one by one if the invalid objects are less. But incase if we have hundreds of invalids, we have to prepare a script and compile all invalids by executing the prepared script.

Use below steps to prepare script of all invalid objects and compile.

Step 1:

connect to database as sysdba and execute below query. 

SQL>col owner  for a15
SQL>col object_name for a35
SQL>col object_type for a25
SQL>col status for a15

SQL>select owner,object_name,object_type,status from dba_objects where status='INVALID';

SQL>spool compile_invalids.sql
SQL>select 'alter '||object_type|| ' ' || owner ||'.'||object_name || ' compile;' from dba_objects where status='INVALID';

SQL>spool off;

Step 2:

execute above prepared script as sysdba to compile all invalid objects.

SQL>compile_invalids.log
SQL>@compile_invalids.sql
SQL>spool off;

Verify compile_invalids.log file if any errors occurred and check the invalid objects count in the database.

Thanks for going through the post......