flashback version query某表一段时间内的变化记录

flashback version query

1参数准备

         相对flashback query只能看到某一时间点的对象状态，oracle 10g引入的flashback version query可以看到去过某个时间段内，记录是如何发生变化的，根据这个历史，DBA就可以快速的判断数据是在什么时间点发生了错误，进而恢复到之前的状态。

         当然和flashback query一样需要设置两个参数：

         UNDO_MANAGEMENT = AUTO

         undo_retention = 1800;   #这个时间可以随便设，他表示在系统中保留提交了的UNDO信息的时间，1800就是保留30分钟。

2环境准备

sys@ORCL>create table zhong1(x int);

Table created.

 

sys@ORCL>insert into zhong1 values(1);

1 row created.

sys@ORCL>commit;

Commit complete.

 

sys@ORCL>update zhong1 set x=2;

1 row updated.

sys@ORCL>commit;

Commit complete.

 

sys@ORCL>update zhong1 set x=4 where x=2;

1 row updated.

sys@ORCL>commit;

Commit complete.

 

3利用flashback version query查看期间变化

sys@ORCL>col versions_starttime for a30

sys@ORCL>col versions_endtime for a30

sys@ORCL>l

  1  select versions_starttime,versions_endtime,versions_xid,versions_operation,x from zhong1

  2  versions between scn minvalue and maxvalue

  3* order by versions_starttime

sys@ORCL>/

 

VERSIONS_STARTTIME    VERSIONS_ENDTIME           VERSIONS_XID     V          X

------------------------------ ------------------------------ ---------------- - ----------

02-MAY-16 06.05.46 AM   02-MAY-16 06.06.22 AM        15000700BA000000 I          1

02-MAY-16 06.06.22 AM   02-MAY-16 06.06.49 AM       10001600B3000000 U          2

02-MAY-16 06.06.49 AM                              0E0003008B000000 U          4

 

 

----当然也可以利用时间查询

sys@ORCL>l

  1  select versions_starttime,versions_endtime,versions_xid,versions_operation,x from zhong1

  2  versions between timestamp to_timestamp('2016-05-02 06:06:30','yyyy-mm-dd hh24:mi:ss') and to_timestamp('2016-05-02 06:07:00','yyyy-mm-dd hh24:mi:ss')

  3* order by versions_endtime

sys@ORCL>/

 

VERSIONS_STARTTIME  VERSIONS_ENDTIME          VERSIONS_XID     V          X

------------------------------ ------------------------------ ---------------- - ----------

                      02-MAY-16 06.06.49 AM                                 2

02-MAY-16 06.06.49 AM                           0E0003008B000000 U          4

 

----上面第可以看出，在06:06:49时，用户做了提交，提交后x值为2，是插入还是修改不知道

 

 

----插入一条数据，不提交

sys@ORCL>insert into zhong1 values(3333);

1 row created.

 

----查询下是否有记录

 select versions_starttime, versions_endtime, versions_xid, versions_operation, x

          from zhong1

        versions between scn minvalue and maxvalue

        order by versions_starttime;

 

VERSIONS_STARTTIME    VERSIONS_ENDTIME         VERSIONS_XID     V          X

------------------------------ ------------------------------ ---------------- - ----------

02-MAY-16 06.05.46 AM   02-MAY-16 06.06.22 AM      15000700BA000000 I          1

02-MAY-16 06.06.22 AM   02-MAY-16 06.06.49 AM      10001600B3000000 U          2

02-MAY-16 06.06.49 AM                             0E0003008B000000 U          4

 

----提交后，在查看

sys@ORCL>commit;

Commit complete.

 

sys@ORCL> select versions_starttime, versions_endtime, versions_xid, versions_operation, x

  2   from zhong1

  3     versions between scn minvalue and maxvalue

  4     order by versions_starttime;

VERSIONS_STARTTIME    VERSIONS_ENDTIME          VERSIONS_XID     V          X

------------------------------ ------------------------------ ---------------- - ----------

02-MAY-16 06.05.46 AM   02-MAY-16 06.06.22 AM       15000700BA000000 I          1

02-MAY-16 06.06.22 AM   02-MAY-16 06.06.49 AM       10001600B3000000 U          2

02-MAY-16 06.06.49 AM                              0E0003008B000000 U          4

02-MAY-16 06.39.22 AM                              0D000000C1000000 I       3333

 

 

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

转载于:http://blog.itpub.net/30130773/viewspace-2102644/