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:
- 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.
- 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.
- 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.