Oracle Flashback Technologies - 闪回查询 - abce

Oracle Flashback Technologies - 闪回查询

2015-06-23 17:01 abce 阅读(333) 评论(0) 编辑收藏举报

Oracle Flashback Technologies - 闪回查询

查看表中，某行数据的修改记录

#创建一个表，并插入和修改数据

SQL> create table y3(id int,name varchar2(20));

Table created.

SQL> insert into y3 values(1,'wahaha');

1 row created.

SQL> commit;

Commit complete.

SQL> update y3 set name='nongfushanquan' where id=1;

1 row updated.

SQL> commit;

Commit complete.
 
SQL> update y3 set name='kaifei' where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> update y3 set name='pijiu' where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> update y3 set name='hongniu' where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> update y3 set name='kele' where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL>

#查询

#查询
SQL> select
  2   name,
  3   versions_starttime,
  4   versions_startscn,
  5   versions_endtime,
  6   versions_endscn,
  7   versions_xid,
  8   versions_operation
  9   from y3
 10   versions between scn minvalue and maxvalue
 11   where id = 1
 12   order by 3
 13   /

NAME                 VERSIONS_STARTTIME        VERSIONS_STARTSCN VERSIONS_ENDTIME          VERSIONS_ENDSCN VERSIONS_XID     V
-------------------- ------------------------- ----------------- ------------------------- --------------- ---------------- -
wahaha               23-JUN-15 08.36.07 AM               1466318 23-JUN-15 08.36.46 AM             1466333 0400200065030000 I
nongfushanquan       23-JUN-15 08.36.46 AM               1466333 23-JUN-15 08.37.16 AM             1466347 0A00040015040000 U
kaifei               23-JUN-15 08.37.16 AM               1466347 23-JUN-15 08.37.31 AM             1466355 070018009D030000 U
pijiu                23-JUN-15 08.37.31 AM               1466355 23-JUN-15 08.37.49 AM             1466364 01000D0048030000 U
hongniu              23-JUN-15 08.37.49 AM               1466364 23-JUN-15 08.39.04 AM             1466400 02001B0021040000 U
kele                 23-JUN-15 08.39.04 AM               1466400                                           06000400DC040000 U

6 rows selected.

SQL>

查看语句

select
 name,
 versions_starttime,
 versions_startscn,
 versions_endtime,
 versions_endscn,
 versions_xid,
 versions_operation
 from y3
 versions between scn minvalue and maxvalue
 #versions between timestamp minvalue and maxvalue 也可以根据时间戳来查看
 where id = 1
 order by 3
 /