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.

See Also