Oracle 利用儲存程序定期分割大型日誌表

問題背景

Oracle 資料庫中，交友聊天記錄、簡訊收發日誌、生產系統稽核日誌、動態網站發布日誌等表格會隨時間無限增長。直接 DELETE 並不釋放物理空間（高水位線問題），且效能低落。需要一種自動化、低影響的方式，將歷史資料按年月分割為 log200308、log200309 等獨立表格。

分割策略：重命名法

核心思路：建立同結構新表 → 重命名舊表為歷史表 → 重命名新表為當前表。應用程式僅受影響數秒，無需修改代碼。

步驟詳解

1. 建立新表：CREATE TABLE log_new AS SELECT * FROM log WHERE 1=2; 再補齊約束、索引、預設值。
2. 重命名舊表：RENAME log TO log_202401; 若遇 ORA-00054（資源忙碌），需重試。
3. 重命名新表：RENAME log_new TO log; 完成分割。

儲存程序自動化範例

CREATE OR REPLACE PROCEDURE SP_PARTITION_LOG_TABLE IS
  v_hist_table VARCHAR2(30) := 'LOG_' || TO_CHAR(SYSDATE, 'YYYYMM');
  v_sql        VARCHAR2(500);
BEGIN
  -- 1. 建立新表
  EXECUTE IMMEDIATE 'CREATE TABLE log_new AS SELECT * FROM log WHERE 1=2';
  -- 補齊索引、約束略...

  -- 2. 重命名舊表（含重試邏輯）
  FOR i IN 1..5 LOOP
    BEGIN
      EXECUTE IMMEDIATE 'RENAME log TO ' || v_hist_table;
      EXIT;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -54 THEN -- ORA-00054
          DBMS_LOCK.SLEEP(2);
        ELSE
          RAISE;
        END IF;
    END;
  END LOOP;

  -- 3. 重命名新表
  EXECUTE IMMEDIATE 'RENAME log_new TO log';
  
  -- 4. 記錄分割日誌
  INSERT INTO partition_log VALUES (v_hist_table, SYSDATE);
  COMMIT;
END;
/

排程執行

使用 DBMS_JOB 或 DBMS_SCHEDULER 定時呼叫：

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'JOB_PARTITION_LOG',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'SP_PARTITION_LOG_TABLE',
    start_date      => TRUNC(SYSDATE) + 1/24, -- 凌晨 1 點
    repeat_interval => 'FREQ=MONTHLY; BYMONTHDAY=1; BYHOUR=1',
    enabled         => TRUE
  );
END;
/

優勢與注意事項

• ✅ 近乎零停機，應用無感知
• ✅ 物理空間真正釋放（歷史表可移至廉價儲存）
• ⚠️ 需處理 ORA-00054 重試
• ⚠️ 歷史表索引、權限需同步維護
• ⚠️ 分區表是更現代化的替代方案（Oracle 8i+ 支援）