利用 Oracle 預存程序實現定時分割表：日誌表自動歸檔最佳實踐

痛點：龐大日誌表的管理困境

聊天記錄、簡訊日誌、生產系統 Audit Log、網站發布日誌……隨時間增長至百萬/億級列，導致：

• 查詢緩慢 (全表掃描)
• 索引膨脹、維護窗口不足
• DELETE 釋放不了空間 (High Water Mark 問題)
• 備份/還原耗時過長

為何不用 DELETE / TRUNCATE / Partition?

方案：RENAME + CTAS (Create Table As Select) 搭配預存程序自動化，零授權成本、極低鎖持有時間、歷史表獨立管理。

核心邏輯：三步驟原子化切換

1. 建立新空表 LOG_NEW (結構、索引、約束同 LOG)。
2. RENAME LOG → LOG_YYYYMMDD (歷史表命名含日期)。
3. RENAME LOG_NEW → LOG (新表上線)。

關鍵：RENAME 是資料字典操作，極快 (毫秒級)，但會因 DML 鎖衝突失敗 (ORA-00054)，需重試機制。

完整預存程序實作

1. 通用重試重命名程序

CREATE OR REPLACE PROCEDURE rename_table (
  p_source  IN VARCHAR2,
  p_target  IN VARCHAR2,
  p_times   IN OUT NUMBER
) IS
  v_sql     VARCHAR2(4000);
  CURSOR c_src IS SELECT 1 FROM user_segments WHERE segment_name = UPPER(p_source);
  CURSOR c_tgt IS SELECT 1 FROM user_segments WHERE segment_name = UPPER(p_target);
  v_dummy   c_src%ROWTYPE;
  v_dummy2  c_tgt%ROWTYPE;
BEGIN
  OPEN c_src; FETCH c_src INTO v_dummy;
  OPEN c_tgt; FETCH c_tgt INTO v_dummy2;
  CLOSE c_src; CLOSE c_tgt;

  IF c_src%FOUND AND c_tgt%NOTFOUND THEN
    v_sql := 'ALTER TABLE ' || p_source || ' RENAME TO ' || p_target;
    EXECUTE IMMEDIATE v_sql;
    DBMS_OUTPUT.PUT_LINE('RENAME SUCCESS: ' || p_source || ' -> ' || p_target);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    p_times := p_times + 1;
    IF p_times < 100 THEN
      DBMS_OUTPUT.PUT_LINE('RETRY ' || p_times || ': ' || SQLERRM);
      rename_table(p_source, p_target, p_times);  -- 遞迴重試
    ELSE
      DBMS_OUTPUT.PUT_LINE('RENAME FAILED AFTER 100 RETRIES: ' || SQLERRM);
      RAISE;
    END IF;
END rename_table;
/

2. 日誌分割主程序

CREATE OR REPLACE PROCEDURE log_history IS
  v_sql      VARCHAR2(32767);
  v_ymd      VARCHAR2(8);
  v_times    NUMBER := 0;
BEGIN
  -- 目標歷史表日期 (例：保留 15 天前資料)
  SELECT TO_CHAR(SYSDATE - 15, 'YYYYMMDD') INTO v_ymd FROM DUAL;

  -- 1. 建立新空表 LOG_NEW (結構複製自 LOG，無資料)
  v_sql := 'CREATE TABLE LOG_NEW PCTFREE 10 PCTUSED 80 AS SELECT * FROM LOG WHERE 1=2';
  EXECUTE IMMEDIATE v_sql;

  -- 2. 加上 PK、預設值、索引 (依實際表結構調整)
  v_sql := 'ALTER TABLE LOG_NEW ADD CONSTRAINT LOG_' || v_ymd || '_PK PRIMARY KEY (ID) TABLESPACE INDX NOLOGGING PCTFREE 10';
  EXECUTE IMMEDIATE v_sql;

  v_sql := 'ALTER TABLE LOG_NEW MODIFY LOGTIME DEFAULT SYSDATE';
  EXECUTE IMMEDIATE v_sql;

  v_sql := 'CREATE INDEX LOG_' || v_ymd || '_LOGTIME ON LOG_NEW(LOGTIME) TABLESPACE INDX NOLOGGING PCTFREE 10';
  EXECUTE IMMEDIATE v_sql;

  -- 3. 重命名舊表為歷史表 (含重試)
  rename_table('LOG', 'LOG_' || v_ymd, v_times);

  -- 4. 新表上線
  EXECUTE IMMEDIATE 'ALTER TABLE LOG_NEW RENAME TO LOG';

  DBMS_OUTPUT.PUT_LINE('LOG HISTORY COMPLETED: LOG_' || v_ymd);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('LOG_HISTORY ERROR: ' || SQLERRM);
    RAISE;
END log_history;
/

權限需求：CREATE ANY TABLE 系統權限

預存程序執行時 角色權限失效，執行用戶必須被直接授予 CREATE ANY TABLE 系統權限 (非透過 Role)。

GRANT CREATE ANY TABLE TO mis_admin;

排程自動化

• Linux/Unix：crontab -e → 0 0 1 * * sqlplus mis_admin/pwd @log_history.sql (每月 1 號 00:00)。
• Windows：工作排程器 → 執行 sqlplus 批次檔。
• Oracle 10g+：改用 DBMS_SCHEDULER 內建排程，更易管理。

多表擴展與維運建議

• 依樣畫葫蘆建立 LOG_HISTORY_XXX 程序，對應不同日誌表。
• 歷史表 (LOG_YYYYMMDD) 可獨立壓縮、移至唯讀表空間、匯出歸檔、甚至不同儲存層級。
• 監控 V$LOCKED_OBJECT、V$SESSION 檢測長期阻塞 Session，分割前先 ALTER SYSTEM KILL SESSION 清理。
• 應用程式若有 Bug 導致長期鎖定，需修正程式碼，否則分割會持續失敗。

優缺點總結

結語

「RENAME + CTAS + 預存程序重試」是處理大量日誌表分割的經典模式，特別適合中小企業、無 Partitioning Option 授權、或需極簡架構的場景。配合排程自動化與監控告警，即可構建「設定後即忘記」的日誌生命週期管理體系。