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         4SQL> insert into A values(5);已创建 1 行。SQL> select * from A;        ID        ----------         2         1         3         4         5SQL> commit;提交完成。SQL> select ora_rowscn, id from A;1 998755 12 998781 853 998781 864 998801 985 998801 99

 

2.利用以下语句伪列查出数据SCN区间，找到startSCN最大值1098443 与 最小值1098556SQL>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 12 08001700AC020000 998781  Insert 863 08001700AC020000 998781  Insert 854 05002400AB020000 998801  Insert 995 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';