Oracle 一鍵重編譯所有無效物件

快速腳本法

利用 DBMS_METADATA 或查詢 ALL_OBJECTS 產生 ALTER ... COMPILE 腳本：

-- 產生腳本
SET HEAD OFF FEED OFF PAGES 0
SPOOL recompile.sql
SELECT 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
FROM   ALL_OBJECTS
WHERE  STATUS = 'INVALID'
AND    OBJECT_TYPE IN ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER','VIEW','SYNONYM','TYPE','TYPE BODY');
SPOOL OFF
@recompile.sql

PL/SQL 程序法（含錯誤記錄）

CREATE OR REPLACE PROCEDURE Compile_All_Invalid IS
  v_sql   VARCHAR2(500);
  v_err   VARCHAR2(4000);
BEGIN
  FOR obj IN (
    SELECT OBJECT_TYPE, OWNER, OBJECT_NAME
    FROM   ALL_OBJECTS
    WHERE  STATUS = 'INVALID'
    AND    OBJECT_TYPE IN ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER','VIEW','TYPE','TYPE BODY')
    ORDER BY DECODE(OBJECT_TYPE,'PACKAGE',1,'PACKAGE BODY',2,'TYPE',3,'TYPE BODY',4,5)
  ) LOOP
    v_sql := 'ALTER ' || obj.OBJECT_TYPE || ' ' || obj.OWNER || '.' || obj.OBJECT_NAME || ' COMPILE';
    BEGIN
      EXECUTE IMMEDIATE v_sql;
    EXCEPTION
      WHEN OTHERS THEN
        v_err := SQLERRM;
        DBMS_OUTPUT.PUT_LINE('FAILED: ' || v_sql || ' - ' || v_err);
        -- 可選：寫入錯誤日誌表
    END;
  END LOOP;
END;
/

執行與驗證

SET SERVEROUTPUT ON
EXEC Compile_All_Invalid;

-- 驗證剩餘無效物件
SELECT OWNER, OBJECT_TYPE, OBJECT_NAME
FROM   ALL_OBJECTS
WHERE  STATUS = 'INVALID';

進階：UTL_RECOMP 並行重編譯

Oracle 10g+ 內建套件，可多執行緒並行重編譯，大幅縮短時間：

-- 並行重編譯所有無效物件（使用 4 執行緒）
EXEC UTL_RECOMP.RECOMP_PARALLEL(4);

-- 或重編譯特定 Schema
EXEC UTL_RECOMP.RECOMP_SERIAL('SCOTT');

重編譯後查詢 DBA_ERRORS 確認殘留錯誤。