闪回版本查询(Flashback Version Query)

闪回版本查询是一个Oracle提供的一个比较有用的功能。我们可以用它来查看某一段时期内表数据所发生的变化。
以下是示例：

SQL> create table t2 as select rownum pkid, lpad('1', rownum, 'A') str from dual
 connect by rownum <= 5;

SQL> col str format a10
SQL> col pkid format 999

SQL> select * from t2;
PKID STR
---- ----------
   1 1
   2 A1
   3 AA1
   4 AAA1
   5 AAAA1

SQL> select dbms_flashback.get_system_change_number from dual;


GET_SYSTEM_CHANGE_NUMBER
------------------------
              1.4183E+13


SQL> select to_char(dbms_flashback.get_system_change_number) from dual;


TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE
----------------------------------------
14183304683078

SQL> update t2 set str=str||'B';


5 rows updated.


SQL> commit;


Commit complete.


SQL> select to_char(dbms_flashback.get_system_change_number) from dual;


TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE
----------------------------------------
14183304683178

SQL> select pkid,str from t2 versions between scn 14183304683078 and 14183304683178;

PKID STR
---- ----------
   5 AAAA1B
   4 AAA1B
   3 AA1B
   2 A1B
   1 1B
   1 1
   2 A1
   3 AA1
   4 AAA1
   5 AAAA1


10 rows selected.

可以看到，闪回版本查询精确的反映了数据前后的变化。

官方介绍是这么说的：

Oracle Flashback Version Query - retrieve metadata and historical data for a specific time interval. You can view all the rows of a table that ever existed during a given time interval. Metadata about the different versions of rows includes start and end time, type of change operation, and identity of the transaction that created the row version. You use the VERSIONS BETWEEN clause of the SELECT statement to create a Flashback Version Query.

当然，闪回版本查询与闪回事务查询一样，支持SCN与timestamp作为一个时间段。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8520577/viewspace-1977092/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8520577/viewspace-1977092/