Flashback Version Query 闪回版本查询
使用Flashback Version Query? 返回在指定时间间隔或SCN间隔内的所有版本，一次commit命令就会创建一个版本。
?
语法如下：
SELECT?.....FROM tablename VERSIONS {BETWEEN {SCN | TIMESTAMP} start AND end}?
--start,end可以是时间也可以是scn
Flashback Version Query伪列说明
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;??--创建表xyc_t1
Table created.
SQL> select to_date(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;?--查询时间作为timestamp开始时间
TO_DATE(SYSDATE,'YY
-------------------
2013-10-06 08:17:58
SQL> insert into xyc_t1 select * from emp where empno=7902;???--插入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;??????????????????????????????????????????????????????????????????????????--再次更改empno=7788的行提交，使这行有旧版本
Commit complete.
SQL> select to_date(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;?--查询时间作为timestamp结束时间
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');
*/
?
SQL> select versions_startscn,empno
? 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');
VERSIONS_STARTSCN VERSIONS_STARTTIME???????????? VERSIONS_ENDSCN VERSIONS_ENDTIME?????????????? VERSIONS_XID???? VERSIONS_OPERATION??????? EMPNO
----------------- ------------------------------ --------------- ------------------------------ ---------------- -------------------- ----------
????????? 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
--可以看出，一次commit是一个版本，当前版本的versions_endscn和versions_endtime值为空，旧版本则有值。
Flashback Transaction Query闪回事务查询
Flashback Transaction Query实际上是查询的数据字典flashback_transaction_query。可以根据flashback_transaction_query 的undo_sql列值返回数据以前版本。
flashback_transaction_query 列说明：
SQL> desc flashback_transaction_query
?Name????????????????????????????????????? Null???? Type
?----------------------------------------- -------- ----------------------------
?XID?????????????????????????????????????????????????????? RAW(8)?????????????????--事务ID
?START_SCN???????????????????????????????????????? ?NUMBER?????????????? ?--事务起始SCN，即第一个dml的SCN
?START_TIMESTAMP???????????????????????????? DATE??????????????????????--事务其实时间戳,即第一个dm的时间戳
?COMMIT_SCN????????????????????????????????????? NUMBER??????????????? --提交事务时的SCN
?COMMIT_TIMESTAMP???????????????????????? DATE??????????????????????-- 提交事务时的时间戳
?LOGON_USER??????????????????????????????????????VARCHAR2(30)?????? --本次事务的用户
?UNDO_CHANGE#????????????????????????????????NUMBER????????????????--撤销SCN
?OPERATION?????????????????????????????????????????VARCHAR2(32)?????? --执行的dml操作：DELETE,INSERT,UPDATE,BEGIN,UNKNOWN
?TABLE_NAME???????????????????????????????????????VARCHAR2(256)???? --dml更改的表
?TABLE_OWNER?????????????????????????????????????VARCHAR2(32)????? --表的所有者
?ROW_ID??????????????????????????????????????????????? VARCHAR2(19)?????? --修改行的ROWID
?UNDO_SQL?????????????????????????????????????????? VARCHAR2(4000)?? -?-撤销dml的sql语句
?
--使用闪回事务查询前，必须启用重做日志流的其他日志记录，重做日志流与Log Miner使用的数据相同，只是接口不同。
示例说明：
SQL> conn / as sysdba;
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;???? --更改值sal=9999
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;? --更改值sal=99999
1 row updated.
SQL> commit;
Commit complete.
SQL> select to_date(sysdate,'YY
-------------------
2013-10-06 10:14:23
?
SQL> select versions_startscn,empno
? 2? from xyc_t1 versions between timestamp to_timestamp('2013-10-06 10:14:04','YYYY-MM-DD HH24:MI:SS');
VERSIONS_STARTSCN VERSIONS_STARTTIME???????????? VERSIONS_ENDSCN VERSIONS_ENDTIME?????????????? VERSIONS_XID???? VERSIONS_OPERATION??????? EMPNO
----------------- ------------------------------ --------------- ------------------------------ ---------------- -------------------- ----------
???1035726???????????????????? 06-OCT-13 10.14.17 AM??????????????????????????????????????????????????????????????????????????????????????????? ?0400040097020000 U??????????????????????????????????????? 7902
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? 7698
????????????????????????????????????????????????????????????????????????????????????? 1035726??????????????????? 06-OCT-13 10.14.17 AM??????????????????????????????????????????????????????????????????????????????????????? 7902
--查询事务id
?
SQL> select start_scn,commit_scn,logon_user,operation,table_name,undo_sql
? 2? from flashback_transaction_query
? 3? where xid=hextoraw('0400040097020000');
?START_SCN COMMIT_SCN LOGON_USER?????????? OPERATION??????????? TABLE_NAME?????????? UNDO_SQL
---------- ---------- -------------------- -------------------- -------------------- --------------------------------------------------
?? 1035724??? 1035726?????????? SCOTT????????????????????????????????? ?UPDATE?????? XYC_T1?????????????? update "SCOTT"."XYC_T1" set "SAL" = '9999' where R
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ?OWID = 'AAASNYAAEAAAAIVAAD';
?
--查出undo sql
?
SQL> update "SCOTT"."XYC_T1" set "SAL" = '9999' where ROWID = 'AAASNYAAEAAAAIVAAD';
1 row updated.
SQL> select * from xyc_t1;
select * from xyc_t1;
???? EMPNO ENAME??????????????? JOB?????????????????????? MGR HIREDATE?????????????????? SAL?????? COMM???? DEPTNO
---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ----------
????? 7698 BLAKE??????????????? MANAGER????????????????? 7839 1981-05-01 00:00:00?????? 2850??????????????????? 30
????? 7902 FORD???????????????? ANALYST??????????????????? 7566 1981-12-03 00:00:00?????? 9999??????????????????? 20
--返回到上一版本，即sal值为9999
