編譯Oracle中無效的對象的N中方法

 

編譯無效的對象常用方法在資料庫中，會存在一些無效的對象，導致這種現象的發生原因很多，其中最常見的就是資料庫升級(例如修改了表的結構)，遷移而引起。有兩種編譯無效對象的方式： 1 使用alter **** compile 語句進行編譯 2 以SYSDBA使用者，執行ORACLE_HOME/rdbms/admin/utlrp.sql 指令碼 3 用DBMS_UTILITY包來進行編譯. 具體使用哪種，根據實際情況選擇。 查詢無效對象SQL: 

SELECT COUNT (*)  FROM user_objects  WHERE object_type IN ('PROCEDURE','FUNCTION','TRIGGER','VIEW','PACKAGE')    AND status = 'INVALID';

  在SQL*plus中利用中間指令碼編譯編寫SQL*Plus指令碼，它可以幫組你掃描非法的指令碼並嘗試重新編譯它們：建立指令碼reCompile.sql

SET feedback OFF SET heading OFF SET linesize 1000 SET pagesize 0 SET pause OFF SET trimspool ON SET verify OFF spool tmp.sql; SELECT 'alter '||object_type||' '||owner||'.'||object_name||' compile;'  FROM all_objects  WHERE status = 'INVALID'    AND object_type in        ('FUNCTION','JAVA SOURCE','JAVA CLASS','PROCEDURE','PACKAGE','TRIGGER'); SELECT 'alter package '||owner||'.'||object_name||' compile body;'  FROM all_objects  WHERE status = 'INVALID'    AND object_type = 'PACKAGE BODY'; spool OFF; @tmp.sql

在SQL*Plus中@reCompile.sql 當你啟動並執行時候，這個指令碼將會建立第二個指令碼，這個指令碼叫做Tmp.sql。它發布所有的ALTER命令然後運行這個指令碼。 編寫PL/SQL利用遊標編譯在上面的方法中，只能知道某某編譯失敗，不清楚失敗原因，可以用PL/SQL實現更詳細的錯誤資訊。

DECLARE  v_objname        user_objects.object_name%TYPE;  v_objtype        user_objects.object_type%TYPE;  CURSOR cur IS     SELECT object_name,object_type       FROM USER_OBJECTS      WHERE status = 'INVALID'        AND object_type in           ('FUNCTION','JAVA SOURCE','JAVA CLASS','PROCEDURE','PACKAGE','TRIGGER'); BEGIN  OPEN cur;  LOOP     FETCH cur into v_objname, v_objtype;     EXIT WHEN cur%NOTFOUND;     BEGIN       EXECUTE Immediate 'alter ' || v_objtype || ' ' || v_objname||' Compile';       dbms_output.put_line('編譯' || v_objtype || ' ' || v_objname || '()成功');     EXCEPTION       WHEN OTHERS THEN         dbms_output.put_line('編譯' || v_objtype ||' ' || v_objname || '()失敗.' || SQLERRM);     END;  END LOOP;  CLOSE cur; END;

當然這個PL/SQL可以輕鬆的修改為procedure或者function，看你喜歡怎麼用了。  Raymond提出的方法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; 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();