flashback version query和Flashback Transaction Query(10g)

Flashback Version Query
可以看到过去某个时间段内记录从而找到恢复点来恢复数据

alter session set nls_date_format='YYYY-MM-DD hh24:mi:ss';
select sysdate from dual;

1.
SQL> select * from A;
        ID
        ----------
         2
         1
         3
         4
SQL> insert into A values(5);
已创建 1 行。
SQL> select * from A;
        ID
        ----------
         2
         1
         3
         4
         5
SQL> commit;
提交完成。
SQL> select ora_rowscn, id from A;
1 998755 1
2 998781 85
3 998781 86
4 998801 98
5 998801 99

 

2.利用以下语句伪列查出数据SCN区间，找到startSCN最大值1098443 与 最小值1098556
SQL>Select versions_xid,versions_startscn,versions_endscn,
   DECODE(versions_operation,'I','Insert','U','Update','D','Delete', 'Original') "Operation", id from A versions between scn minvalue and maxvalue;

1 03000D00A6020000 998755  Insert 1
2 08001700AC020000 998781  Insert 86
3 08001700AC020000 998781  Insert 85
4 05002400AB020000 998801  Insert 99
5 05002400AB020000 998801  Insert 98

3.通过以下语句来查出时间回滚数据，找到恢复时间具体定位数据后，进行恢复回滚中的数据
SQL>select xid,commit_scn,commit_timestamp,operation,undo_sql
from flashback_transaction_query q where q.xid in(select versions_xid from a versions between scn 998755 and 998801);

8 03000D00A6020000 998755 2011-2-17 11:01:54 BEGIN 
7 03000D00A6020000 998755 2011-2-17 11:01:54 INSERT delete from "PKUWH_ZA"."A" where ROWID = 'AAAM1gAAFAAAAEhAAA';
3 08001700AC020000 998781 2011-2-17 11:02:42 BEGIN 
1 08001700AC020000 998781 2011-2-17 11:02:42 INSERT delete from "PKUWH_ZA"."A" where ROWID = 'AAAM1gAAFAAAAEiAAB';
2 08001700AC020000 998781 2011-2-17 11:02:42 INSERT delete from "PKUWH_ZA"."A" where ROWID = 'AAAM1gAAFAAAAEiAAA';
6 05002400AB020000 998801 2011-2-17 11:03:00 BEGIN 
4 05002400AB020000 998801 2011-2-17 11:03:00 INSERT delete from "PKUWH_ZA"."A" where ROWID = 'AAAM1gAAFAAAAEjAAB';
5 05002400AB020000 998801 2011-2-17 11:03:00 INSERT delete from "PKUWH_ZA"."A" where ROWID = 'AAAM1gAAFAAAAEjAAA';