oracle 表误 delete/drop/truncate 的还原

我们很看一下数据表的 delete 还原



查看数据
select * from system.test where object_id = 2;
SYS C_OBJ# 2 2 CLUSTER 2011/9/17 9:46:13 2011/9/17 9:46:13 2011-09-17:09:46:13 VALID N N N 5


删除数据




SQL> delete system.test where object_id = 2;


1 row deleted.


SQL> commit;


Commit complete.


SQL> 




切一下当前归档


SQL> alter system switch logfile;


System altered.


批量挖掘一下归档
过程略




定位删除语句的scn号


select scn from system.mylog l where l.seg_name = 'TEST' and l.operation_code = 2
15170041460496


将数据闪回到临时表中


SQL> create table system.mytest as select * from system.test  as of scn 15170041460496;


Table created.


验证数据




SQL> select count(*) from system.mytest where object_id = 2;


  COUNT(*)
----------
         1


可见我们恢复成功

我们再看一下 drop 的还原




建立测试表
SQL> create table system.test as select * from dba_objects;


Table created.




将表drop 掉


SQL> drop table system.test;


Table dropped.


分析归档, 找到drop 的scn号


select * from system.mylog l where l.seg_name = 'TEST'


定位到scn号是 15170041468515


将表进行闪回


SQL> flashback table system.test to before drop rename to test222;


Flashback complete.


进行验证




SQL> select count(*) from system.test222;


  COUNT(*)
----------
     80091


恢复成功

我们再看一下truncate 的还原




建立测试表


SQL> create table system.test as select * from dba_objects;


Table created.


truncate 表


SQL> truncate table system.test;


Table truncated.


运行包 RecoverTruncate 表




SQL> @ RecoverTruncate.pck              


Package created.




Package body created.


执行恢复


SQL> exec RecoverTruncate('system','test');




PL/SQL procedure successfully completed.


查看表数据


SQL> select count(*)  from system.test;


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


SQL> select count(*)  from system.test$$;


  COUNT(*)
----------
     80123


由于insert 比较慢,可以将system.test 的索引与表 drop 掉,然后 CTA进行插入,之后再建立索引既可.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7569309/viewspace-2132604/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7569309/viewspace-2132604/