闪回版本查询

DBAs can use Oracle Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A row version is created whenever a COMMIT statement is executed.
闪回版本的作用是查询指定行的不同的版本数据,也就是指定行在过去的不同值
规则：（查询真实表）
SELECT [pseudo_columns]...FROM table_name
VERSION BETWEEN
{SCN | TIMESTAMP {expr | MINVALUE} AND
{expr | MAXVALUE}}
[AS OF {SCN|TIMESTAMP expr}]
WHERE [pseudo_column | column] . .
虚列
VERSIONS_STARTSCN The SCN at which this version of the row was created
VERSIONS_STARTTIME The time stamp at which this version of the row was created
VERSIONS_ENDSCN The SCN at which this row no longer existed (either changed or deleted)
VERSIONS_ENDTIME The time stamp at which this row no longer existed (either changed or deleted)
VERSIONS_XID The transaction ID of the transaction that created this version of the rows
VERSIONS_OPERATION The operation done by this transaction: I=Insert, D=Delete, U=Update
MINVALUE and MAXVALUE resolve to the SCN or time stamp of the oldest and most recent data available, respectively

下面是一个比较典型的闪回版本查询

COLUMN versions_startscn FORMAT 99999999999999999
COLUMN versions_starttime FORMAT A24
COLUMN versions_endscn FORMAT 99999999999999999
COLUMN versions_endtime FORMAT A24
COLUMN versions_xid FORMAT A16
COLUMN versions_operation FORMAT A1
COLUMN description FORMAT A11
SET LINESIZE 200
SELECT versions_startscn, 
     versions_starttime,
     versions_endscn, 
     versions_endtime,
     versions_xid, 
     versions_operation,
     last_name, salary
FROM personnel
VERSIONS BETWEEN TIMESTAMP
    TO_TIMESTAMP('2007-03-21 19:30:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2007-03-22 04:30:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE first_name = 'Anthony';

也可以使用MINVALUE和MAXVALUE定界

SQL> SELECT versions_xid AS XID, versions_startscn AS START_SCN, versions_endscn AS END_SCN, versions_operation AS OPERATION, first_name FROM employees VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE AS OF SCN 5525300 WHERE employee_id = 111;

SQL> select versions_startscn startscn,versions_endscn endscn, versions_xid xid, versions_operation oper, employee_id empid, last_name name, salary sal from hr.employees versions between timestamp trunc(systimestamp) and systimestamp where employee_id = 124;

Example

SQL> SELECT * FROM test1;
        ID DESCR
---------- ------------------------------
         1 One
         2 Two
         3 Three
SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;
CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
----------- ---------------------------------------------------------------------------
   29006118 15-AUG-11 12.51.21.000000000 PM
SQL> UPDATE test1 SET descr='The one' WHERE id=1;
1 ROW updated.
SQL> COMMIT;
COMMIT complete.
SQL> UPDATE test1 SET descr='The only one' WHERE id=1;
1 ROW updated.
SQL> COMMIT;
COMMIT complete.
SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;
CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
----------- ---------------------------------------------------------------------------
   29006142 15-AUG-11 12.52.03.000000000 PM

Remark:
Please note the commit after each update to generate multiple transactions.

You can now see past figures:

SQL> SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, id, descr
     FROM test1
     VERSIONS BETWEEN SCN 29006118 AND 29006142
     WHERE id = 1;
VERSIONS_STARTSCN VERSIONS_STARTTIME       VERSIONS_ENDSCN VERSIONS_ENDTIME         VERSIONS_XID     V         ID DESCR
----------------- ------------------------ --------------- ------------------------ ---------------- - ---------- ------------------------------
         29006133 15-AUG-11 12.51.45 PM                                             0A001500B99F0000 U          1 The only one
         29006128 15-AUG-11 12.51.36 PM           29006133 15-AUG-11 12.51.45 PM    060017000A100000 U          1 The one
                                                  29006128 15-AUG-11 12.51.36 PM                                1 One
SQL> SELECT * FROM test1 AS OF scn 29006128;
        ID DESCR
---------- ------------------------------
         1 The one
         2 Two
         3 Three
SQL> SELECT * FROM test1 AS OF scn 29006118;
        ID DESCR
---------- ------------------------------
         1 One
         2 Two
         3 Three
SQL> SELECT * FROM test1;
        ID DESCR
---------- ------------------------------
         1 The only one
         2 Two
         3 Three

Remark:
Same as DBMS_FLASHBACK package you may work with timestamp or SCN.

http://blog.yannickjaquier.com/oracle/flashback-technologies-by-examples.html#Flashback_Versions_Query
https://oracle-base.com/articles/10g/flashback-10g#flashback_version_query
http://www.oracle-dba-online.com/flash_back_features.htm

来自为知笔记(Wiz)