DBA在一些场合下,要对无效的数据库对象进行重新编译.如果只是对all_objects等视图中选出来的无效对象进行重新编译,可能需要很多次:因为各个对象之间有依赖性的.这样并不是一个很好的办法.Raymond 在Recompiling invalid objects提到了如何有效地重新编译无效对象.提到了三种比较有效地方法(
- 利用$ORACLE_HOME/rdbms/admin下的utlrp.sql脚本编译.一般都是在迁移或者升级之后运行该脚本.Raymond说该方法的不足之处在于这个脚本是对整个数据库中的对象进行重新编译的,所以不可取.有网友指出utlrp.sql 实际上是调用utlrcmp.sql的这样就可以用utl_recomp包来做喽(这样就是比较好的方法).
- 用DBMS_UTILITY包来进行编译.但是也有一定的局限性.
- Raymond提到了自己的解决办法: 不过也立刻有人指出来,这样对 View 的重新编译是无能为力的(ALTER_COMPILE只能处理:PACKAGE, PACKAGE BODY, PROCEDURE, FUNCTION, TRIGGER).参见下面的脚本
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;
调用 utl_recomp 包是比较好的做法.需要 sys 权限. BTW:刚才浏览eygle 的站点,发现他有一 Blog 说的正是这个事情: http://www.eygle.com/archives/2005/01/ecioaeaoeeeaoea.html ,晕倒Rem EXAMPLES Rem 1. Recompile all objects sequentially: Rem execute utl_recomp.recomp_serial(); Rem Rem 2. Recompile objects in schema SCOTT sequentially: Rem execute utl_recomp.recomp_serial('SCOTT'); Rem Rem 3. Recompile all objects using 4 parallel threads: Rem execute utl_recomp.recomp_parallel(4); Rem Rem 4. Recompile objects in schema JOE using the number of threads Rem specified in the paramter JOB_QUEUE_PROCESSES: Rem execute utl_recomp.recomp_parallel(NULL, 'JOE'); Rem Rem 5. Recompile all objects using 2 parallel threads, but allow Rem other applications to use the job queue concurrently: Rem execute utl_recomp.recomp_parallel(2, NULL, Rem utl_recomp.share_job_queue); Rem Rem 6. Restore the job queue after a failure in recomp_parallel: Rem execute utl_recomp.restore_job_queue();