SQL>
SQL> select to_char( versions_starttime,'yyyymmdd hh24:mi:ss') ,to_char( versions_endtime,'yyyymmdd hh24:mi:ss'),versions_xid,versions_operation,id,name,age from student
2 versions between scn minvalue and maxvalue;
TO_CHAR(VERSIONS_STARTTIME,'YY TO_CHAR(VERSIONS_ENDTIME,'YYYY VERSIONS_XID VERSIONS_OPERATION ID NAME AGE
------------------------------ ------------------------------ ---------------- ------------------ ----------- -------------------- -----------
20150124 19:52:54 0B00080005010000 I 2 mazi 24
20150124 19:52:15 13000100FE000000 U 1 wangwu 23
20150124 19:51:29 20150124 19:52:15 12001F00FF000000 I 1 zhangsan 23
SQL>
SQL> select to_char( versions_starttime,'yyyymmdd hh24:mi:ss') ,to_char( versions_endtime,'yyyymmdd hh24:mi:ss'),versions_xid,versions_operation,id,name,age from student
2 versions between timestamp minvalue and maxvalue;
TO_CHAR(VERSIONS_STARTTIME,'YY TO_CHAR(VERSIONS_ENDTIME,'YYYY VERSIONS_XID VERSIONS_OPERATION ID NAME AGE
------------------------------ ------------------------------ ---------------- ------------------ ----------- -------------------- -----------
20150124 19:52:54 0B00080005010000 I 2 mazi 24
20150124 19:52:15 13000100FE000000 U 1 wangwu 23
20150124 19:51:29 20150124 19:52:15 12001F00FF000000 I 1 zhangsan 23
SQL>
过一段时间你会发现查询的结果变少了：
SQL> select to_char( versions_starttime,'yyyymmdd hh24:mi:ss') ,to_char( versions_endtime,'yyyymmdd hh24:mi:ss'),versions_xid,versions_operation,id,name,age from student
2 versions between timestamp minvalue and maxvalue;
TO_CHAR(VERSIONS_STARTTIME,'YY TO_CHAR(VERSIONS_ENDTIME,'YYYY VERSIONS_XID VERSIONS_OPERATION ID NAME AGE
------------------------------ ------------------------------ ---------------- ------------------ ----------- -------------------- -----------
2 mazi 24
1 wangwu 23
SQL>
这是因为undo_retention这个值设置的时间到期了。
oracle10g的闪回事务查询：
Oracle10g可以进行基于闪回版本查询的恢复，就是闪回事务查询。
从flashback_transaction_query中查询引起数据变化的
事务，和撤销事务的SQL语句，就是查询 operation和undo_sql 列。