Oracle 無效物件重新編譯完整指令集：utlrp.sql 與 ALTER COMPILE 詳解

情境說明

資料庫升級、Patch 套用、Schema 變更等操作後，常導致 PL/SQL 物件（Procedure、Function、Package、Trigger、Type、View）變為 INVALID 狀態。Oracle 提供兩種主要方式重新編譯：自動化腳本 utlrp.sql 以及手動 ALTER ... COMPILE 指令。

方法一：執行 utlrp.sql 批量編譯所有無效物件

適用版本：Oracle 8i 及以上版本

腳本位置：$ORACLE_HOME/rdbms/admin/utlrp.sql

執行步驟：

1. 以 SYSDBA 身分登入 SQL*Plus
2. 執行 @$ORACLE_HOME/rdbms/admin/utlrp.sql

腳本會平行編譯資料庫中所有現有的 PL/SQL 模組，並自動處理相依關係，是資料庫升級後的標準作業。

方法二：針對特定物件手動編譯

當僅需修復少數物件，或 utlrp.sql 執行後仍有殘留無效物件時，使用手動指令：

Stored Procedure

ALTER PROCEDURE procedure_name COMPILE;

Function

ALTER FUNCTION function_name COMPILE;

Package Specification

ALTER PACKAGE package_name COMPILE;

Package Body

ALTER PACKAGE package_name COMPILE BODY;

Trigger

ALTER TRIGGER trigger_name COMPILE;

View

ALTER VIEW view_name COMPILE;

Type

ALTER TYPE type_name COMPILE;

進階技巧

• 加入 DEBUG 選項：ALTER PROCEDURE p1 COMPILE DEBUG; 保留除錯資訊。
• 指定 PLSQL_CODE_TYPE：ALTER PROCEDURE p1 COMPILE PLSQL_CODE_TYPE=NATIVE; 切換為原生編譯。
• 檢查編譯錯誤：編譯後執行 SELECT * FROM USER_ERRORS WHERE NAME = 'OBJECT_NAME'; 查看詳細錯誤訊息。
• 批量產生編譯腳本：

  SELECT 'ALTER ' || OBJECT_TYPE || ' ' || OBJECT_NAME || ' COMPILE;'
  FROM USER_OBJECTS
  WHERE STATUS = 'INVALID'
  ORDER BY OBJECT_TYPE, OBJECT_NAME;

最佳實務建議

1. 資料庫升級/遷移後，優先執行 utlrp.sql。
2. 定期排程檢查 DBA_OBJECTS 中 STATUS = 'INVALID' 的物件。
3. 手動編譯時，先編譯 Specification 再編譯 Body（Package），避免相依錯誤。
4. 生產環境編譯前務必評估鎖定影響，建議在維護窗口執行。

結語

掌握 utlrp.sql 與 ALTER COMPILE 兩套機制，能快速恢復 PL/SQL 物件效能，確保應用程式穩定運行。