(轉)編譯Oracle中無效的物件的N中方法

mahanso發表於2011-03-15
編譯Oracle物件
一、編譯無效的物件常用方法
在資料庫中，會存在一些無效的物件，導致這種現象的發生原因很多，其中最常見的就是資料庫升級(例如修改了表的結構)，遷移而引起。
有兩種編譯無效物件的方式：
 
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();

文章轉自：http://blog.csdn.net/z3h/archive/2007/09/18/1790378.aspx非常感謝作者分享

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12272958/viewspace-689511/，如需轉載，請註明出處，否則將追究法律責任。

相關文章