Oracle 無效物件批量編譯指南：UTLRP、DBMS_UTILITY、動態 SQL 與依賴關係分析

Oracle 無效物件批量編譯指南：UTLRP、DBMS_UTILITY、動態 SQL 與依賴關係分析

物件失效常見原因

1. 被引用物件結構變更 (DDL) → 級聯失效。
2. 部署腳本未充分測試 → 編譯錯誤。
3. 升級/遷移後大量失效。
4. 分區表操作未指定 UPDATE INDEXES 導致全域索引 UNUSABLE (屬於失效一種)。

查詢無效物件

SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY 1,2,3;

查看依賴關係

SELECT * FROM dba_dependencies WHERE name = UPPER('&object_name');
-- 或 ALL_DEPENDENCIES / USER_DEPENDENCIES

四種編譯方案對比

方案詳解與範例

1. 手工編譯 (針對性強)

ALTER PACKAGE schema.pkg_name COMPILE;
ALTER PACKAGE schema.pkg_name COMPILE BODY;
ALTER VIEW schema.view_name COMPILE;
ALTER TRIGGER schema.trg_name COMPILE;

2. UTLRP.sql (官方標準流程)

EXEC DBMS_UTILITY.COMPILE_SCHEMA('SCOTT', FALSE); -- FALSE=不檢查依賴順序(舊版)，TRUE=檢查(新版建議)

4. 動態 SQL 腳本 (可部署為作業)

-- 生成腳本法
SET HEADING OFF FEEDBACK OFF ECHO OFF LINES 999
SPOOL compile_invalid.sql
SELECT 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE IN ('PACKAGE','FUNCTION','PROCEDURE','TRIGGER','VIEW','JAVA SOURCE','JAVA CLASS');
SPOOL OFF
SET HEADING ON FEEDBACK ON ECHO ON
@compile_invalid.sql

5. PL/SQL 迴圈法 (含錯誤記錄)

DECLARE
  v_objname USER_OBJECTS.OBJECT_NAME%TYPE;
  v_objtype USER_OBJECTS.OBJECT_TYPE%TYPE;
  CURSOR c_inv IS
    SELECT OBJECT_NAME, OBJECT_TYPE
    FROM USER_OBJECTS
    WHERE STATUS = 'INVALID'
    AND OBJECT_TYPE IN ('FUNCTION','JAVA SOURCE','JAVA CLASS','PROCEDURE','PACKAGE','TRIGGER','VIEW');
BEGIN
  FOR r IN c_inv LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ALTER ' || r.OBJECT_TYPE || ' ' || r.OBJECT_NAME || ' COMPILE';
      DBMS_OUTPUT.PUT_LINE('OK: ' || r.OBJECT_TYPE || ' ' || r.OBJECT_NAME);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('FAIL: ' || r.OBJECT_TYPE || ' ' || r.OBJECT_NAME || ' - ' || SQLERRM);
    END;
  END LOOP;
END;
/
-- 同理可改查 DBA_OBJECTS 處理跨 Schema

最佳實踐建議

1. 升級/遷移後**首選 UTLRP**，並行編譯最快且依賴正確。
2. 日常單 Schema 維護用 DBMS_UTILITY.COMPILE_SCHEMA。
3. 編譯失敗物件需檢查錯誤堆疊 (USER_ERRORS)，修正代碼/依賴後再編譯。
4. 建立定期檢查無效物件的排程作業，配合告警郵件。