oracle10g的闪回事务查询：
Oracle10g可以进行基于闪回版本查询的恢复，就是闪回事务查询。
从flashba
oracle11G闪回flashback(二)
ate student s set s.name='wangwu' where s.id = 1; 1 row updated
SQL> commit; Commit complete
SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 3115954
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YYYYMMDDHH24: ------------------------------ 20150124 19:52:26
SQL> insert into student values (2,'mazi',24); 1 row inserted
SQL> commit; Commit complete
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YYYYMMDDHH24: ------------------------------ 20150124 19:52:58
SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 3116062
SQL> SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,id,name,age from student 2 versions between scn minvalue and maxvalue; VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION ID NAME AGE -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- ------------------ ----------- -------------------- ----------- 24-1月 -15 07.52.54 下午 0B00080005010000 I 2 mazi 24 24-1月 -15 07.52.15 下午 13000100FE000000 U 1 wangwu 23 24-1月 -15 07.51.29 下午 24-1月 -15 07.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 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的闪回事务查询：