利用 PL/SQL 存儲程序實現日誌表定期分割歸檔

為何需要分割日誌表

聊天記錄、簡訊收發、系統稽核日誌等資料隨時間快速增長。單純 DELETE 釋放不了空間（高水位標記問題），且效能差。採用 RENAME 切換新舊表，應用程式無感知，僅影響數秒。

核心邏輯：重命名重試機制

OLTP 環境下 DML 可能導致 ORA-00054 資源忙碌。提供 rename_table 程序，遇鎖最多遞迴重試 100 次：

CREATE OR REPLACE PROCEDURE rename_table (
    source_name IN VARCHAR2,
    target_name IN VARCHAR2,
    times       IN OUT NUMBER
) IS
    query_str   VARCHAR2(4000);
    src_exist   BOOLEAN;
    tgt_exist   BOOLEAN;
BEGIN
    SELECT COUNT(*) INTO src_exist FROM user_segments WHERE segment_name = UPPER(source_name);
    SELECT COUNT(*) INTO tgt_exist FROM user_segments WHERE segment_name = UPPER(target_name);

    IF src_exist AND NOT tgt_exist THEN
        query_str := 'ALTER TABLE ' || source_name || ' RENAME TO ' || target_name;
        EXECUTE IMMEDIATE query_str;
        DBMS_OUTPUT.PUT_LINE('RENAME SUCCESS!');
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        times := times + 1;
        IF times < 100 THEN
            rename_table(source_name, target_name, times);
        ELSE
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
            DBMS_OUTPUT.PUT_LINE('ERROR OVER 100 TIMES, EXIT');
        END IF;
END;
/

分割主程序：log_history

CREATE OR REPLACE PROCEDURE log_history IS
    qstr      VARCHAR2(32767);
    ymd       VARCHAR2(8);
    retry_cnt NUMBER := 0;
BEGIN
    SELECT TO_CHAR(SYSDATE-15, 'YYYYMMDD') INTO ymd FROM dual;

    -- 1. 建立新表結構（不含資料）
    qstr := 'CREATE TABLE log_new PCTFREE 10 PCTUSED 80 AS SELECT * FROM log WHERE 1=2';
    EXECUTE IMMEDIATE qstr;

    -- 2. 加上 PK、預設值、索引
    qstr := 'ALTER TABLE log_new ADD CONSTRAINT log_'||ymd||'_pk PRIMARY KEY (id) TABLESPACE indx NOLOGGING PCTFREE 10';
    EXECUTE IMMEDIATE qstr;
    qstr := 'ALTER TABLE log_new MODIFY logtime DEFAULT SYSDATE';
    EXECUTE IMMEDIATE qstr;
    qstr := 'CREATE INDEX log_'||ymd||'_logtime ON log_new(logtime) TABLESPACE indx NOLOGGING PCTFREE 10';
    EXECUTE IMMEDIATE qstr;

    -- 3. 將舊表改名為歷史表
    rename_table('log', 'log'||ymd, retry_cnt);

    -- 4. 新表改名為正式表
    EXECUTE IMMEDIATE 'ALTER TABLE log_new RENAME TO log';
END;
/

權限與排程

• 執行使用者需具備 CREATE ANY TABLE 系統權限（非透過 Role 授予）
• OS 層 crontab 每月 1 日 00:00 執行：sqlplus -s / as sysdba @exec_log_history.sql

異常處理：長期鎖定偵測與清除

-- 查詢長期未釋放的鎖
SELECT t2.username, t2.sid, t2.serial#, t2.logon_time
FROM   v$locked_object t1, v$session t2
WHERE  t1.session_id = t2.sid
ORDER BY t2.logon_time;

-- 強制中斷異常 Session
ALTER SYSTEM KILL SESSION 'sid,serial#';

建議在分割程序啟動前先清理殘留鎖定。