oracle闪回查询

浏览数：17 / 时间：2015年06月12日

一、引言

程序中用到需要同步oracle更新和删除数据，于是考虑利用oracle的闪回查询机制来实现。

利用该机制首先需要oracle启用撤销表空间自动管理回滚信息，并根据实际情况设置对数据保存的有效期，即对数据的操作保存多久？

查看撤销表信息undo： show parameter undo;

设置撤销表信息：

alter system set undo_managerment=auto;设置为auto才可以使用闪回查询

alter system set undo_retention=900;最长保留时间（单位秒）

alter system set undo_tablespace=undotbs1;

二、闪回查询的使用

参考：

http://www.oracle-developer.net/display.php?id=320

http://www.oracle-developer.net/display.php?id=210

flashback version query 通过where字句扩展VERSION BETWEEN来实现。

Flashback version query is invoked using the new VERSIONS BETWEEN extension to the FROM clause. It takes two forms as follows

VERSIONS BETWEEN TIMESTAMP [lower bound] AND [upper bound]; or
VERSIONS BETWEEN SCN [lower bound] AND [lower bound].

例子：

在表上执行了一次插入、两次更新、一次删除

SELECT x, y, z
FROM   fbt VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
ORDER  BY y;

结果：

         X Y                         Z
---------- ------------------------- -----------------------
         1 10-AUG-2005 18:00:57.078  Initial population
         1 10-AUG-2005 18:01:07.109  First update
         1 10-AUG-2005 18:01:17.125  Second update
         1 10-AUG-2005 18:01:17.125  Second update

4 rows selected.

三、闪回查询机制实现原理

As stated above, Oracle provides a variety of metadata with each version of our data. The metadata is exposed via a number of pseudo-columns that we can use with our flashback version queries. These pseudo-columns are as follows:
就像上面显示的，oracle对于我们数据的每个版本提供了一个元信息。这些元信息通过一些伪列显示出来，我们可以在闪回查询中使用这些伪列。这些伪列如下：

VERSIONS_STARTTIME (start timestamp of version); 版本开始时间戳
VERSIONS_STARTSCN (start SCN of version); 版本开始系统更改号（SCN：system change number）
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). 版本操作类型（包括增、删、改）
We can now include some of these pseudo-columns in our flashback version query as follows. Note the SCN metadata is excluded as we are using timestamps for the examples.
现在我们可以在下面的闪回查询sql语句中包含这些伪列，没有包括SCN元信息的原因是我们使用了时间戳在这个例子中。

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.
This explains why we were seeing the second update row twice. If we look at the VERSIONS_OPERATION column, we can see that the second appearance of the final update record is actually the delete operation against it (specified by ‘D‘). Without the versions metadata, the Y timestamp column was actually confusing us into thinking we had two versions of the same record at the same time.
这里解释了为什么在上面我们看到了两行更新操作。如果我们看一下VERSION_OPERATION列，我们就可以看到第二次更新实际上是以操作类型D即delete来记录的。除了元信息，Y列的时间戳也使我们很迷惑，两次版本的时间是一样的。

The metadata also gives us an indication that the delete operation was the final version of this data. The end timestamp of the version is NULL which tells us that there is no superceding record.
元信息也告诉我们删除操作是关于这个数据的最后一个版本。这个版本的结束时间戳是NULL也告诉了我们这是一个没有后续操作的记录。

Interestingly, if we had not included a sleep between creating the FBT table and adding the single record, it would be likely (based on observations) that all VERSIONS_* pseudo-columns (except the ENDTIME and ENDSCN) would be NULL for the insert record.
有趣的是，如果我们没有在创建fbt表和插入一个记录时间做休眠，它就可能会显示出（根据观察）所有以VERSIONS_开头的伪列（除了ENDTIME和ENDSCN）可能都是NULL对于这次插入。