Flashback Query
Flashback Query
概要 UNDOテーブルスペースのRETENTION範囲内に(領域オーバーで消えることがあるが)、テーブルの履歴が確認できる。
文法
Flashback Version Query
select column_name from table_name [VERSIONS BETWEEN TIMESTAMP [lower bound] AND [upper bound] | VERSIONS BETWEEN SCN [lower bound] AND [lower bound]];
Flashback Transaction Query
select column_name from table_name [VERSIONS BETWEEN TIMESTAMP [lower bound] AND [upper bound] | VERSIONS BETWEEN SCN [lower bound] AND [lower bound]];
詳細説明
下記の仮想カラム(versions pseudo-columns)が追加された。
VERSIONS_STARTTIME (start timestamp of version);
VERSIONS_STARTSCN (start SCN of version);
VERSIONS_ENDTIME (end timestamp of version);
VERSIONS_ENDSCN (end SCN of version);
VERSIONS_XID (transaction ID of version); and
VERSIONS_OPERATION (DML operation of version).
サンプル
versions pseudo-columns SQL> SELECT z 2 , VERSIONS_STARTTIME 3 , VERSIONS_ENDTIME 4 , VERSIONS_XID 5 , VERSIONS_OPERATION 6 FROM fbt VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE 7 ORDER BY 8 VERSIONS_ENDTIME; Z VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION -------------------- ------------------------- ------------------------- ---------------- ------------------ Initial population 10-AUG-2005 18:00:53.000 10-AUG-2005 18:01:05.000 040026008A010000 I First update 10-AUG-2005 18:01:05.000 10-AUG-2005 18:01:14.000 040029008A010000 U Second update 10-AUG-2005 18:01:14.000 10-AUG-2005 18:01:26.000 040027008A010000 U Second update 10-AUG-2005 18:01:26.000 040028008A010000 D 4 rows selected.