Oracle 批次重編譯無效存儲過程：SQL*Plus 腳本與 PL/SQL 封裝版

場景

大量物件變更（如升級、遷移、DDL 批次執行）導致大量 Procedure/Function/Package/Trigger 狀態變為 INVALID，需一次性重編譯。

方法一：SQL*Plus 動態生成腳本

SPOOL recompile_procs.sql
SELECT 'ALTER PROCEDURE ' || object_name || ' COMPILE;'
FROM   all_objects
WHERE  status = 'INVALID'
AND    object_type = 'PROCEDURE';
SPOOL OFF
@recompile_procs.sql

同理可生成 FUNCTION、PACKAGE、TRIGGER、VIEW、TYPE 等類型。

方法二：PL/SQL 封裝程序（可捕獲錯誤、記錄日誌）

CREATE OR REPLACE PROCEDURE zl_compile_invalid_procedure AS
  l_sql VARCHAR2(200);
BEGIN
  FOR r IN (
    SELECT object_name, object_type
    FROM   all_objects
    WHERE  status = 'INVALID'
    AND    object_type IN ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER','TYPE','TYPE BODY')
  ) LOOP
    l_sql := 'ALTER ' || r.object_type || ' ' || r.object_name || ' COMPILE';
    BEGIN
      EXECUTE IMMEDIATE l_sql;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('FAILED: ' || l_sql || ' - ' || SQLERRM);
    END;
  END LOOP;
END;
/
-- 執行前開啟輸出
SET SERVEROUTPUT ON
EXEC zl_compile_invalid_procedure;

進階：UTLRP.SQL 官方一鍵重編譯

@?/rdbms/admin/utlrp.sql

遞迴重編譯所有無效物件，平行度由 CPU_COUNT 決定，為**生產環境首選**。

驗證

SELECT object_name, object_type, status
FROM   all_objects
WHERE  status = 'INVALID';

應無記錄返回。