[bbk4732] 第25集 - 第三章 Flashback Table 02

Flashback Version Query

 

/******************************************/

实验：跟踪数据不同版本之间的事物变化

目的：利用flashback query data,使用dml进行数据恢复

/******************************************/

SQL> show user
USER is "U2"
SQL> create table m(id int,name varchar2(10));

Table created.

SQL> insert into m values(0,'arcerzhang');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into m values(1,'maryhu');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into m values(2,'lydiazhang');

1 row created.

SQL> commit;

Commit complete.

SQL> select versions_Xid,name FROM m
  2  VERSIONS BETWEEN scn 4332165 AND 4332242;

VERSIONS_XID     NAME
---------------- ----------
09000600B9080000 lydiazhang
07001A0008080000 maryhu
0400010002080000 arcerzhang

SQL> conn /as sysdba
Connected.
SQL> !clear


SQL> select dbms_flashback.get_system_change_number() from dual;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
                                  4332165

SQL> select dbms_flashback.get_system_change_number() from dual;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
                                  4332227

SQL> select dbms_flashback.get_system_change_number() from dual;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
                                  4332234

SQL> select dbms_flashback.get_system_change_number() from dual;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
                                  4332242

必须是事务提交的数据，才会记录SCN版本号. 

SELECT versions_xid,versions_starttime,versions_endtime, name FROM M
VERSIONS BETWEEN TIMESTAMP TO_DATE('2013-05-16 16:00:00','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2013-05-16 16:10:00','yyyy-mm-dd hh24:mi:ss') 
WHERE id = 0;

VERSIONS_XID     VERSIONS_STARTTIME        VERSIONS_ENDTIME          NAME
---------------- ------------------------- ------------------------- ----------
0A00140029080000 16-MAY-13 04.09.11 PM                               arcerzqj
0400030005080000 16-MAY-13 04.08.56 PM     16-MAY-13 04.09.11 PM     arcerzhang
06001000C5080000 16-MAY-13 04.08.47 PM     16-MAY-13 04.08.56 PM     arcer
                                           16-MAY-13 04.08.47 PM     arcerzhang