ORACLE 利用SCN恢复误delete的表
时间：2021-07-01 10:21:17
帮助过：2人阅读
--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的表
标签：
