Compiling Invalid Objects in Oracle Database

After schema changes packages and package bodies go invalid

How to validate them in one go

Choice

1. You can go to $ORACLE_HOME/rdbms/admin
As SYS user run @utlrp.sql and check invalids again

NOTE- This might need to be run multiple times

2. Run the following query

invalid.sql
 

Set heading off;

set feedback off;

set echo off;

Set lines 999;

 

Spool run_invalid.sql

 

select

   'ALTER ' || OBJECT_TYPE || ' ' ||

   OWNER || '.' || OBJECT_NAME || ' COMPILE;'

from

   dba_objects

where

   status = 'INVALID'

and

   object_type in ('PACKAGE','FUNCTION','PROCEDURE')

;

 

spool off;

 

set heading on;

set feedback on;

set echo on;

 

@run_invalid.sql