ORACLE 利用SCN恢复误delete的表

--kg是误删除的表 SQL> select count(*) from kg;   COUNT(*) ----------     820861 SQL> delete from kg; 820861 rows deleted SQL> commit; Commit complete SQL> select count(*) from kg;   COUNT(*) ----------          0 SQL> rollback; Rollback complete SQL> select count(*) from kg;   COUNT(*) ----------          0 --kg被删除且已经提交。
--查询此时数据库的SCN SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------                  4915780 SQL> select count(*) from kg as of scn 4915780;   COUNT(*) ----------          0 --创建表howard存放数据库的SCN SQL> create table howard(count int,scn int); Table created SQL> DECLARE      i INT:=4915600;      BEGIN         FOR i IN 4915600..4915782 LOOP           INSERT INTO howard( SCN) VALUES (i );           UPDATE howard SET COUNT =(SELECT COUNT(*) FROM kg AS OF SCN i) WHERE SCN= i;         END LOOP ;      END ;      / SQL> SELECT * FROM howard SQL> /
                                  COUNT                                     SCN --------------------------------------- ---------------------------------------                                  820861                                 4915704                                  820861                                 4915705                                  820861                                 4915706                                       0                                 4915707                                       0                                 4915708                                       0                                 4915709
--得到删除前数据库的SCN，恢复表数据 SQL>  select count(*) from kg  as of scn 4915706;   COUNT(*) ----------     820861 SQL> select count(*) from kg  as of scn 4915707;   COUNT(*) ----------          0 SQL> insert into kg select * from kg as of scn 4915706; 820861 rows inserted SQL> drop table howard; Table dropped SQL> commit; Commit complete

ORACLE 利用SCN恢复误delete的表

标签：

本文系统来源：http://www.cnblogs.com/sddychj/p/4627337.html

【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！