用闪回恢复delete,insert,update表（学习手记1)

1.修改库的归档模式

startup mount -> alter database archivelog  ->alter system switch logfile(手动归档切换)生成归档文件

2.

select name,first_change# fscn,next_change# nscn,first_time from v$archived_log

1    +DG1/devdb/1_41_789857151.dbf    1241938    1253322    2012-8-9 上午 05:23:19
2    +DG1/devdb/1_42_789857151.dbf    1253322    1253503    2012-8-10 上午 05:58:03
3    +DG1/devdb/1_43_789857151.dbf    1253503    1253521    2012-8-10 上午 05:59:49
4    +DG1/devdb/2_22_789857151.dbf    1197906    1253519    2012-8-3 上午 04:52:10
5    +DG1/devdb/2_23_789857151.dbf    1253519    1253856    2012-8-10 上午 05:59:52

3.create table aa as select * from dba_objects;

SQL> delete from aa where rownum<1200;

1199 rows deleted.

SQL> commit;

Commit complete.

SQL> delete from aa where rownum<5000;

4999 rows deleted.

4.发现表的数据不对了，误delete;

先获取当前SCN，作参考

SQL> select dbms_flashback.get_system_change_number fscn from dual;

      FSCN
----------
   1256223

5.然后根据归档的时间和FSCN号码进行反复尝试，达到一个认为满意的值时为止;

  1* select count(*) from aa as of scn &scn
SQL> /
Enter value for scn: 1253521
old   1: select count(*) from aa as of scn &scn
new   1: select count(*) from aa as of scn 1253521

  COUNT(*)
----------
     50658

SQL> /
Enter value for scn: 1253519
old   1: select count(*) from aa as of scn &scn
new   1: select count(*) from aa as of scn 1253519

  COUNT(*)
----------
     50658

SQL> /
Enter value for scn: 1253503
old   1: select count(*) from aa as of scn &scn
new   1: select count(*) from aa as of scn 1253503

  COUNT(*)
----------
     50658

6. 再创建一张临时存数据的表

crate table aa2 as select * from aa where 1=2;

然后将确定的SCN值插回aa2表做两表的对比，

将少的数据插回表中即可;

二.当中间出现过DDL操作对此表(eg.truncate)，那么再用闪回时将报

              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

三。此操作对于insert,update同样生效，操作类似;

四。闪回本质是利用UNDO表空间内容;