Generate script to compile invalid objects

Sometimes some database object(s) stays invalid (there is a lot of reason: modification object, compiling new procedure, function, package (depending exists) etc).

Easy method is compile @$ORACLE_HOME/rdbms/admin/utlrp.sql or detect invalid objects and recompile it. But it is dangerous every time to use utlrp, it is recommended to use utlrp when upgrade db version ex : 9 > 10 > 11 (also release upgrade). Because while upgrade process DB is not available to use for end user.

But if invalid object count is not big and not a few you can use below single SQL and compile result of it.

SQL> select 'ALTER TRIGGER '||OWNER||'.'||OBJECT_NAME||' COMPILE;'
from dba_objects where status='INVALID' AND OBJECT_TYPE='TRIGGER'
union
select 'ALTER FUNCTION '||OWNER||'.'||OBJECT_NAME||' COMPILE;'
from dba_objects where status='INVALID' AND OBJECT_TYPE='FUNCTION'
union
select 'ALTER PACKAGE '||OWNER||'.'||OBJECT_NAME||' COMPILE BODY;'
from dba_objects where status='INVALID' AND OBJECT_TYPE='PACKAGE BODY'
union
select 'ALTER PROCEDURE '||OWNER||'.'||OBJECT_NAME||' COMPILE;'
from dba_objects where status='INVALID' AND OBJECT_TYPE='PROCEDURE'

EX:

ALTER FUNCTION ULFET.TEST_FUNC COMPILE;
ALTER PACKAGE ULFET.LOG_TOOL COMPILE BODY;
ALTER PROCEDURE ULFET.UPDATE_EMP COMPILE;
ALTER PROCEDURE ULFET.ADD_EMP COMPILE;
ALTER TRIGGER ULFET.TAKE_OLD_VALUE COMPILE;

Also you can use DBMS_DDL and DBMS_UTILITY packages:

EXEC DBMS_DDL.ALTER_COMPILE('PACKAGE', 'ULFET', 'LOG_TOOL');
or
EXEC DBMS_UTILITY.COMPILE_SCHEMA('ULFET');

Also you can create PLSQL anonymous block and compile it.

SQL> set serverouput on
SQL> DECLARE
   comp_pack        VARCHAR2 (100);
   comp_pack_body   VARCHAR2 (200);
   comp_view        VARCHAR2 (200);
   comp_proc        VARCHAR2 (200);
   comp_trig        VARCHAR2 (200);
   comp_func        VARCHAR2 (200);
BEGIN
   FOR c IN (  SELECT * FROM dba_objects WHERE status = 'INVALID' ORDER BY object_type)
   LOOP
      BEGIN
         --generate compile statement
         comp_pack :='alter package '|| c.owner|| '.'|| c.object_name|| ' compile;';
         comp_pack_body :='alter package '|| c.owner|| '.'|| c.object_name|| ' compile body;';
         comp_view :='alter view ' || c.owner || '.' || c.object_name || ' compile;';
         comp_proc :='alter procedure '|| c.owner|| '.'|| c.object_name|| ' compile;';
         comp_func :='alter function '|| c.owner|| '.'|| c.object_name|| ' compile;';
         comp_trig :='alter trigger '|| c.owner|| '.'|| c.object_name|| ' compile;';
         DBMS_OUTPUT.put_line ('Compile -> ' || c.object_name || ' type : ' || c.object_type);
         --compile
         IF c.object_type = 'PACKAGE' THEN
            EXECUTE IMMEDIATE comp_pack;
         ELSIF c.object_type = 'PACKAGE BODY' THEN
            EXECUTE IMMEDIATE comp_pack_body;
         ELSIF c.object_type = 'VIEW' THEN
            EXECUTE IMMEDIATE comp_view;
         ELSIF c.object_type = 'PROCEDURE' THEN
            EXECUTE IMMEDIATE comp_proc;
         ELSIF c.object_type = 'FUNCTION' THEN
            EXECUTE IMMEDIATE comp_func;
         ELSIF c.object_type = 'TRIGGER' THEN
            EXECUTE IMMEDIATE comp_trig;
         END IF;
         --catch exception and show
      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.put_line ('Compile ERROR : '|| c.owner|| '.'|| c.object_name|| ' type => '|| c.object_type);
      END;
   END LOOP;
END;


Read it in azerbaijani : http://www.slideshare.net/ulfettanriverdiyev/invalid-obyektlerin-kampilasiyasi