您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

一行 SQL 秒查 Oracle 列建立時間：ORA_ROWSCN 與 SCN_TO_TIMESTAMP 實戰

編輯：Oracle 資料庫基礎

核心原理

Oracle 為每列隱含儲存 ORA_ROWSCN (System Change Number) — 該列最後異動時的資料庫 SCN。透過 SCN_TO_TIMESTAMP 即可換算為人類可讀時間。

魔法 SQL

SELECT
  ename,
  TO_CHAR(SCN_TO_TIMESTAMP(ORA_ROWSCN), 'YYYY-MM-DD HH24:MI:SS') AS insert_time
FROM scott.emp;

關鍵限制：預設為區塊級 SCN

預設建表時，ORA_ROWSCN 記錄的是資料區塊的 SCN，同一區塊內所有列共享同一 SCN → 時間不精確。
唯有建表時指定 ROWDEPENDENCIES，才啟用列級 SCN，精確到每列。

驗證與對比

-- 無 ROWDEPENDENCIES (預設)
CREATE TABLE t1 (id NUMBER, txt VARCHAR2(20));
INSERT INTO t1 VALUES (1, 'a');
INSERT INTO t1 VALUES (2, 'b');
SELECT id, ORA_ROWSCN FROM t1;
-- 兩列 ORA_ROWSCN 相同

-- 有 ROWDEPENDENCIES
CREATE TABLE t2 (id NUMBER, txt VARCHAR2(20)) ROWDEPENDENCIES;
INSERT INTO t2 VALUES (1, 'a');
-- 稍等片刻
INSERT INTO t2 VALUES (2, 'b');
SELECT id, ORA_ROWSCN FROM t2;
-- 兩列 ORA_ROWSCN 不同，SCN_TO_TIMESTAMP 可見時間差

適用場景與替代方案

場景	方案	備註
新建表需審計列建立時間	建表加 `ROWDEPENDENCIES` + `ORA_ROWSCN`	零額外儲存、零應用改動
既有表無 ROWDEPENDENCIES	1. 增加 `CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP` 欄位 2. 啟用 `FLASHBACK DATA ARCHIVE` (Total Recall) 3. LogMiner 分析歸檔日誌	方案 1 最輕量；方案 2 需 Enterprise Edition
需查詢歷史所有版本時間	Flashback Query: `SELECT * FROM t AS OF TIMESTAMP ...`	依賴 UNDO 保留時間

注意事項

SCN_TO_TIMESTAMP 精度約 3 秒，且 SCN 與時間映射僅保留約 5 天 (由 MAX_SCN_AGE 控制)，超過範圍報 ORA-08181。
大量 DML 可能導致 SCN 增長過快，縮短可查詢時間窗口。
ROWDEPENDENCIES 會增加每列 6 位元組儲存開銷，大表需評估空間影響。