Recompiling invalid objects

I recently witnessed a developer recompiling his schema’s INVALID objects by selecting all the invalid objects from the user_objects table and compiling them. This he had to do 3 or 4 times after each other before all the objects were valid. The reason for the objects not all becoming valid the first time round is because in Oracle all objects that are dependant on another object that has become invalid it too becomes invalid. Although invalid PL/SQL modules get automatically recompiled on use, it is useful to ensure that all objects are valid ahead of time, this will eliminate or minimize subsequent latencies caused due to on-demand automatic recompilation at runtime. To be able to recompile all invalid objects in one go you need to compile the objects in the correct order of dependency. How does one do that?

I know of three possible approaches:

1. In $ORACLE_HOME/rdbms/admin you’ll find a sql script called utlrp.sql. This script recompiles all invalid objects in the database, not only one particular schema but every invalid object in the database. To run it you must be connected as sysdba and it must be run in sql*plus. The script is used by Oracle after a migration or upgrade. I personally do not use it as it recompiles all the invalid objects in the whole database and I prefer to recompile only one particular schema at a time.
2. In the supplied package DBMS_UTILITY one finds a procedure COMPILE_SCHEMA (schema varchar2, compile_all boolean default TRUE). The parameter ‘schema’ is the schema whose objects you wish to compile. The parameter ‘compile_all’, if true (default) it will compile all the objects regardless if valid or invalid, if set to false it will only recompile invalid objects. I tried using it in Oracle 8i but it produces problems if the schema had any java objects. I therefore created my own recompile procedure (choice 3) to avoid this problem and have been using it ever since. I also took a quick look at the dbms_utility.compile_schema code and could not see where it obtains the objects correct order to compile and therefore I am not sure if it actually works.
3. Finally my own solution. I use the SYS.DEPENDENCY$ table (need select privilege on this table) to get the objects dependencies and then with the CONNECT BY get them into the correct order.

   CREATE OR REPLACE PROCEDURE RECOMPILE_SCHEMA
   IS
     v_Type USER_OBJECTS.OBJECT_TYPE%TYPE;
     v_Name USER_OBJECTS.OBJECT_NAME%TYPE;
     v_Stat USER_OBJECTS.STATUS%TYPE;
   
     CURSOR c_Obj
     IS
       SELECT  BASE
       FROM  (SELECT A.OBJECT_ID BASE
              ,      B.OBJECT_ID REL
              FROM   USER_OBJECTS A
                 ,      USER_OBJECTS B
                 ,      SYS.DEPENDENCY$  C
              WHERE  A.OBJECT_ID = C.D_OBJ#
              AND    B.OBJECT_ID = C.P_OBJ#
              AND    A.OBJECT_TYPE IN ('PACKAGE',
                                       'PROCEDURE',
                                       'FUNCTION',
                                       'PACKAGE BODY',
                                     --  'VIEW',
                                       'TRIGGER')
              AND    B.OBJECT_TYPE IN ('PACKAGE',
                                       'PROCEDURE',
                                       'FUNCTION',
                                       'PACKAGE BODY',
                                     --  'VIEW',
                                       'TRIGGER')
               AND    NOT A.OBJECT_NAME = B.OBJECT_NAME) OBJECTS
       CONNECT BY BASE = PRIOR REL
       GROUP   BY BASE
       ORDER   BY MAX(LEVEL) DESC;
   BEGIN
     -- loop through all objects in order of dependancy.
     FOR c_Row IN c_Obj
     LOOP
       -- select the objects attributes (type, name & status).
       SELECT OBJECT_TYPE
       ,      OBJECT_NAME
       ,      STATUS
       INTO   v_Type
       ,      v_Name
       ,      v_Stat
       FROM   USER_OBJECTS
       WHERE  OBJECT_ID = c_Row.BASE;
   
       -- if the OBJECT is INVALID, recompile it.
       IF v_Stat = 'INVALID' THEN
         DBMS_DDL.ALTER_COMPILE(v_Type, USER, v_Name);
       END IF;
     END LOOP;
   
     -- Recompile all remaining INVALID OBJECTS (all those without dependencies).
     FOR c_Row IN ( SELECT OBJECT_TYPE
                ,      OBJECT_NAME
                FROM   USER_OBJECTS
                WHERE  STATUS = 'INVALID'
                AND    OBJECT_TYPE IN ('PACKAGE',
                                       'PROCEDURE',
                                       'FUNCTION',
                                       'TRIGGER',
                                       'PACKAGE BODY',
                                    --   'VIEW',
                                       'TRIGGER') )
     LOOP
       DBMS_DDL.ALTER_COMPILE(c_Row.OBJECT_TYPE, USER, c_Row.OBJECT_NAME);
     END LOOP;
   END RECOMPILE_SCHEMA;
   

One could modify this procedure so that it uses the dba_objects view instead of the user_objects view and add to the select statement the object_owner field. Place the procedure in the system schema and then grant execute privileges on it to whoever is allowed to use it.