SPOOL ExecCompProc.sql
SELECT 'ALTER PROCEDURE ' || OBJECT_NAME || ' COMPILE;'
FROM ALL_OBJECTS
WHERE STATUS = 'INVALID' AND OBJECT_TYPE = 'PROCEDURE';
SPOOL OFF
@ExecCompProc.sql
CREATE OR REPLACE PROCEDURE ZL_COMPILE_INVALID_PROCEDURE AS
V_SQL VARCHAR2(200);
BEGIN
FOR X IN (SELECT OBJECT_NAME FROM ALL_OBJECTS
WHERE STATUS = 'INVALID' AND OBJECT_TYPE = 'PROCEDURE') LOOP
V_SQL := 'ALTER PROCEDURE ' || X.OBJECT_NAME || ' COMPILE';
BEGIN
EXECUTE IMMEDIATE V_SQL;
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); -- 記錄失敗對象
END;
END LOOP;
END;
/
-- 執行
EXEC ZL_COMPILE_INVALID_PROCEDURE;
-- 查看輸出需先:SET SERVEROUTPUT ON
方案二優勢:可納入維护作業,異常捕獲不中斷全量編譯,配合 DBMS_OUTPUT 記錄失敗對象便於排查。