Flashback Version Query、Flashback Transaction Query快速闪回细粒度数据
使用，一次commit命令就会创建一个版本。
语法如下：
versions_start{scn|time} 版本开始的scn或时间戳
versions_end{scn|time} 版本结束scn或时间戳，如果有值表明此行后面被更改过是旧版本，如果为null，则说明行版本是当前版本或行被删除（即versions_operation值为D）。
versions_xid 创建行版本的事务ID
versions_operation 在行上执行的操作（I=插入，D=删除，U=更新）
SQL> create table xyc_t1 as select * from emp where 1=2;
Table created.
SQL> select to_date(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
TO_DATE(SYSDATE,'YY
-------------------
2013-10-06 08:17:58
SQL> insert into xyc_t1 select * from emp where empno=7902;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into xyc_t1 select * from emp where empno=7788;
1 row created.
SQL> insert into xyc_t1 select * from emp where empno=7698;
1 row created.
SQL> commit;
Commit complete.
SQL> update xyc_t1 set sal=8888 where empno=7788;
1 row updated.
SQL> commit;
Commit complete.
SQL> select to_date(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
TO_DATE(SYSDATE,'YY
-------------------
2013-10-06 08:20:01
/*
select versions_startscn,versions_starttime,versions_endscn,versions_endtime,versions_xid,versions_operation,empno
from xyc_t1 versions between timestamp to_timestamp('2013-10-06 10:14:04','YYYY-MM-DD HH24:MI:SS') and to_timestamp('2013-10-06 10:14:23','YYYY-MM-DD HH24:MI:SS');
*/
2 from xyc_t1 versions between timestamp to_timestamp('2013-10-06 08:17:58','YYYY-MM-DD HH24:MI:SS') and to_timestamp('2013-10-06 08:20:01','YYYY-MM-DD HH24:MI:SS');
----------------- ------------------------------ --------------- ------------------------------ ---------------- -------------------- ----------
1032654 06-OCT-13 08.19.51 AM 08000E0016030000 U 7788
1032637 06-OCT-13 08.19.14 AM 0600180017030000 I 7698
1032637 06-OCT-13 08.19.14 AM 1032654 06-OCT-13 08.19.51 AM 0600180017030000 I 7788
1032628 06-OCT-13 08.18.47 AM 090014002C030000 I 7902
闪回事务查询
Name Null? Type
----------------------------------------- -------- ----------------------------
XID RAW(8)
START_SCN NUMBER
START_TIMESTAMP DATE
COMMIT_SCN NUMBER
COMMIT_TIMESTAMP DATE
LOGON_USER VARCHAR2(30)
UNDO_CHANGE# NUMBER
OPERATION VARCHAR2(32)
TABLE_NAME VARCHAR2(256)
TABLE_OWNER VARCHAR2(32)
ROW_ID VARCHAR2(19)
UNDO_SQL VARCHAR2(4000)
-撤销dml的sql语句
SQL> alter database add supplemental log data;
Database altered.
SQL> alter database add supplemental log data (primary key) columns;
Database altered.
SQL> grant select any transaction to scott;
Grant succeeded.
SQL> conn scott/xyc
SQL> update xyc_t1 set sal=9999 where empno=7902;
1 row updated.
SQL> commit;
Commit complete.
SQL> select to_date(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
TO_DATE(SYSDATE,'YY
-------------------
2013-10-06 10:14:04
SQL> update xyc_t1 set sal=99999 where empno=7902;
1 row updated.
SQL> commit;
Commit complete.
SQL> select to_date(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
TO_DATE(SYSDATE,'YY
-------------------
2013-10-06 10:14:23
2 from xyc_t1 versions between timestamp to_timestamp('2013-10-06 10:14:04','YYYY-MM-DD HH24:MI:SS') and to_timestamp('2013-10-06 10:14:23','YYYY-MM-DD HH24:MI:SS');
----------------- ------------------------------ --------------- ------------------------------ ---------------- -------------------- ----------
1035726 06-OCT-13 10.14.17 AM 0400040097020000 U 7902
7698
1035726 06-OCT-13 10.14.17 AM 7902
2 from flashback_transaction_query
3 where xid=hextoraw('0400040097020000');
---------- ---------- -------------------- -------------------- -------------------- --------------------------------------------------
1035724 1035726 SCOTT UPDATE XYC_T1 update "SCOTT"."XYC_T1" set "SAL" = '9999' where R
select * from xyc_t1;
---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 9999 20