Compile All Invalid Oracle objects

If you add a column to a table that invalidates lots of packages, triggers, views etc then you'll need to compile all of these invalidated objects. And each of these objects might in turn invalidate more - so this can take a few cycles.





I use the SQL statement below to generate the COMPILE statements for all invalid objects. I then copy and paste the COMPILE statements into a SQL session and run them (compiling them). Then I run the code below again (and compile again...etc) until no more are found.

SELECT 'ALTER '
  ||decode(object_type,'PACKAGE BODY','PACKAGE', object_type)
  ||' '||object_name||' COMPILE'
  ||decode(object_type,'PACKAGE BODY',' BODY', '')||';'
FROM user_objects
WHERE status = 'INVALID' 
AND object_type <> 'SYNONYM'

The output from this (which I paste into a SQL window to run) will be something like:

ALTER TRIGGER CUST_AUI COMPILE;
ALTER PACKAGE CUST_PKG COMPILE;
ALTER PACKAGE CUST_PKG COMPILE BODY;
ALTER VIEW CUST_VW COMPILE;
ALTER PACKAGE CUST_AUDIT_PKG COMPILE BODY;