善用 Oracle 10g ORA_ROWSCN 偽欄位實現資料變更追蹤

背景：資料倉庫增量擷取的挑戰

資料倉庫需從多源系統擷取變更資料。傳統作法需在來源表加入「最後更新時間」欄位，但修改既有應用程式成本高、風險大。Oracle 10g 引入 ORA_ROWSCN 偽欄位，提供無侵入式的變更追蹤能力。

ORA_ROWSCN 機制

• 每列記錄最近一次修改該列的交易對應的系統變更號 (SCN)。
• 預設為區塊層級追蹤：同一資料區塊內所有列共享相同 SCN（保守上界）。
• 可於建表時指定 ROWDEPENDENCIES 啟用列層級追蹤（較精確，但無法事後修改）。

實際操作範例

-- 查詢 SCN
SELECT ora_rowscn, ename, sal FROM emp;

-- 更新單列
UPDATE emp SET sal = 6000 WHERE ename = 'KING';
COMMIT;

-- 再次查詢，所有列 SCN 皆更新（區塊層級特性）
SELECT ora_rowscn, ename, sal FROM emp;

-- SCN 轉換為時間戳
SELECT SCN_TO_TIMESTAMP(653331) FROM dual;
-- 結果：20-JUN-06 11.03.59.000000000 PM

應用場景

1. 增量擷取查詢

-- 擷取指定時間後變更的資料
SELECT * FROM source_table
WHERE ORA_ROWSCN > :last_extract_scn;

2. 閃回查詢捷徑

ORA_ROWSCN 可作為閃回查詢的 SCN 參考值：

SELECT * FROM emp AS OF SCN (653330) WHERE ename = 'KING';
-- 可看到更新前的薪資 5000 與原始 SCN 529426

限制與考量

• 區塊層級預設值：未修改列的 SCN 也會更新，可能導致誤判為變更。若需精確列層級，必須在建表時指定 ROWDEPENDENCIES。
• 時間戳為估計值：SCN_TO_TIMESTAMP 回傳的時間為約略值，非精確交易時間。
• 不可索引：ORA_ROWSCN 無法建立索引，大表查詢需配合分區或其他策略。

結語

ORA_ROWSCN 為無侵入式變更追蹤提供了強大工具，特別適合無法修改來源應用系統的資料倉庫 ETL 場景。搭配 SCN_TO_TIMESTAMP 與閃回查詢，構成完整的歷史資料重建與增量擷取能力。