oracle回滚

oracle 使用DBMS_FLASHBACK恢复意外删除的数据

   dbms_flashback
1> 获得当前SCN

  select dbms_flashback.get_system_change_number from dual;
SQL>  select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                  4672259

2>如果能够确切知道删除之前SCN最好，如果不知道，可以进行闪回查询尝试
   1* select count(*) from wrcdb.templetdic as of scn 4672259
SQL> /

   COUNT(*)
----------
          0

   1* select count(*) from wrcdb.templetdic as of scn 4670800
SQL> /

   COUNT(*)
----------
         42
3>该scn中的数据和删除前一样，就可以用该scn恢复了
SQL> edit
已写入 file afiedt.buf

   1* insert into wrcdb.templetdic select * from wrcdb.templetdic as of scn 4670800
SQL> /

已创建42行。
  太棒了！
delete from answer_data;
commit;
insert into answer_data select * from answer_data as of scn 1928300000;
commit;

 

//

select dbms_flashback.get_system_change_number from dual;
--------1933613010
select count(*) from COMPANY_DETECTION_PARAMETERS as of scn 1933613010;
delete from COMPANY_DETECTION_PARAMETERS;
commit;
insert into COMPANY_DETECTION_PARAMETERS select * from COMPANY_DETECTION_PARAMETERS as of scn 1933610000;
commit;
select count(*) from COMPANY_DETECTION_PARAMETERS;

 

原文出自：http://www.itblog8.cn/sql/20130612160.html