通过闪回查询和事物查询恢复小记

1、

SQL> select count(*)  from  test order by id;

  COUNT(*)
----------
120

 

2、delete from  test where id<50

     commit;

3、

SQL> select dbms_flashback.get_system_change_number from dual;----最好查讯到当前scn，否则有时会出现

ORA-30052: invalid lower limit snapshot expression错误，SQL>alter system set undo_retention=10800 scope=both;

 

GET_SYSTEM_CHANGE_NUMBER
------------------------
    610896

 

4、select versions_xid, versions_operation from TEST versions  between scn 610000 and 610896 order by 2;

versions_xid                 versions_operation

 

06002E0019010000        D

06002E0019010000        D

...................................................................

 

5、select xid,commit_scn,commit_timestamp,operation,undo_sql from flashback_transaction_query q where q.xid='06002E0019010000'

 

6、通过scn闪回，提交的scn为610521，应该闪回到610520

insert into test
 select * from test as of scn 610520
 where id not in (select id from test);

 

7、通过时间闪回

insert into test
 select * from test as of timestamp to_timestamp('2010-5-24 10:29:15', 'yyyy-mm-dd hh24:mi:ss')
 where id not in (select id from test);

 

commit；