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';