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.
DBMS_DDL.ALTER_COMPILE won’t work with views!
Check out this:
http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10802/d_ddl.htm#996814
Regarding your first remark about $ORACLE_HOME\rdbms\admin\utlrp.sql. It’s more handy than you think. Nowadays utlrp.sql calls script utlrcmp.sql . It states in the heading:
NAME
utlrcmp.sql – Utility package for dependency-based recompilation of invalid objects sequentially or in parallel.
DESCRIPTION
This script provides a packaged interface to recompile invalid PL/SQL modules, Java classes, indextypes and operators in a database sequentially or in parallel.
This script is particularly useful after a major-version upgrade. A major-version upgrade typically invalidates all PL/SQL and Java objects. Although invalid objects are recompiled automatically on use, it is useful to run this script ahead of time (e.g. as one of the last steps in your migration), since this will either eliminate or minimize subsequent latencies caused due to on-demand automatic recompilation at runtime.
PARALLELISM AND PERFORMANCE
Parallel recompilation can exploit multiple CPUs to reduce the time taken to recompile invalid objects. The degree of parallelism is specified by the first argument to utl_recomp.recomp_parallel(). In general, a parallelism setting of one thread per available CPU provides a good initial setting.
EXAMPLES
1. Recompile all objects sequentially:
execute utl_recomp.recomp_serial();
2. Recompile objects in schema SCOTT sequentially:
execute utl_recomp.recomp_serial(‘SCOTT’);
3. Recompile all objects using 4 parallel threads:
execute utl_recomp.recomp_parallel(4);
4. Recompile objects in schema JOE using the number of threads
specified in the paramter JOB_QUEUE_PROCESSES:
execute utl_recomp.recomp_parallel(NULL, ‘JOE’);
5. Recompile all objects using 2 parallel threads, but allow
other applications to use the job queue concurrently:
execute utl_recomp.recomp_parallel(2, NULL,utl_recomp.share_job_queue);
6. Restore the job queue after a failure in recomp_parallel:
execute utl_recomp.restore_job_queue();
In other words – after it is installed you can use it to recompile your objects. You need privileges from the SYS environment on the utl_recomp package to use it. An advantage right now is that the source is not WRAPPED, disadvantage – you need the dba to install it. Nethertheless my “buikgevoel”says that it will be production worthy soon. As usual the moment has a “production” marker, the source will be WRAPPED by Oracle…
The Oracle Documentation states that the only types allowed for dbms_ddl.alter_compile are: PACKAGE, PACKAGE BODY, PROCEDURE, FUNCTION, or TRIGGER. So it is not possible to compile invalid views with this procedure.