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(*)

----------

SQL> rollback;

Rollback complete

SQL> select count(*) from kg;

COUNT(*)

----------

--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(*)

----------

--创建表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(*)

----------

SQL> insert into kg select * from kg as of scn 4915706;

820861 rows inserted

SQL> drop table howard;

Table dropped

SQL> commit;

Commit complete