Flashback Query 允許查詢過去某個時間點(或 SCN)的資料狀態,利用 UNDO 區段重建歷史版本。
-- 取得目前 SCN
SELECT dbms_flashback.get_system_change_number FROM dual; -- 1196559
-- 進行更新、刪除、插入並提交
UPDATE flash_tbl SET id=id+100 WHERE id>15; COMMIT;
DELETE flash_tbl WHERE id<5; COMMIT;
INSERT INTO flash_tbl VALUES(300,'r');
INSERT INTO flash_tbl VALUES(500,'t');
-- 取得新 SCN
SELECT dbms_flashback.get_system_change_number FROM dual; -- 1196625
-- 版本查詢:顯示兩個 SCN 之間的所有變更版本
SELECT id, vl, versions_startscn, versions_endscn,
versions_operation, versions_xid
FROM flash_tbl
VERSIONS BETWEEN SCN 1196559 AND 1196625;
VERSIONS_OPERATION:I(插入)、U(更新)、D(刪除)。VERSIONS_STARTSCN/ENDSCN:該版本生效的 SCN 區間。VERSIONS_XID:產生該版本的交易 ID。FLASHBACK_TRANSACTION_QUERY-- 取得最新 SCN
SELECT dbms_flashback.get_system_change_number FROM dual; -- 1200548
DELETE flash_tbl WHERE id=116; COMMIT;
SELECT dbms_flashback.get_system_change_number FROM dual; -- 1200555
-- 需 SYSDBA 授權
GRANT SELECT ANY TRANSACTION TO scott;
-- 查詢交易詳細與回滾 SQL
SELECT xid, commit_scn, commit_timestamp, operation, undo_sql
FROM flashback_transaction_query
WHERE xid IN (
SELECT versions_xid FROM flash_tbl
VERSIONS BETWEEN SCN 1200548 AND 1200555
);
輸出包含 UNDO_SQL,可直接用於人工回滾,功能類似 LogMiner 但更簡便。