oracle scn与数据恢复,基于scn的数据恢复

不小心delete掉一些数据，而且已经commit了，怎么办？我们可以基于回闪(flashback)和scn进行一些数据的恢复。

--查询当前scn

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

4787371

SQL> SELECT COUNT(*) FROM hjm_tb_test01;

COUNT(*)

----------

10072

--模拟误删除数据：

SQL> delete from hjm_tb_test01 where object_id<=5000;

已删除4940行。

SQL> commit;

提交完成。

--删除数据后只有5132行了，原为10072行

SQL> SELECT COUNT(*) FROM hjm_tb_test01;

COUNT(*)

----------

5132

--创建用于恢复的表，将恢复数据插入其中

SQL> create table hjm_tb_test01_recov as select * from hjm_tb_test01 where 1=0;

表已创建。

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

4788873

SQL> select count(*) from hjm_tb_test01 as of scn &scn;

输入 scn 的值: 4788873

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788873

COUNT(*)

----------

5132

SQL> /

输入 scn 的值: 4787873

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4787873

COUNT(*)

----------

10072

SQL> /

输入 scn 的值: 4788872

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788872

COUNT(*)

----------

5132

SQL> /

输入 scn 的值: 4788772

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788772

COUNT(*)

----------

5132

SQL> /

输入 scn 的值: 4787972

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4787972

COUNT(*)

----------

10072

SQL> /

输入 scn 的值: 4788072

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788072

COUNT(*)

----------

10072

SQL>

SQL> /

输入 scn 的值: 4788999

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788999

COUNT(*)

----------

5132

SQL> /

输入 scn 的值: 4788500

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788500

COUNT(*)

----------

5132

SQL> /

输入 scn 的值: 4788700

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788700

COUNT(*)

----------

5132

SQL> /

输入 scn 的值: 4788800

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788800

COUNT(*)

----------

5132

SQL> /

输入 scn 的值: 4788900

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788900

COUNT(*)

----------

5132

SQL> /

输入 scn 的值: 4788950

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788950

COUNT(*)

----------

5132

SQL> /

输入 scn 的值: 4788990

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788990

COUNT(*)

----------

5132

SQL> /

输入 scn 的值: 4788995

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788995

COUNT(*)

----------

5132

SQL> /

输入 scn 的值: 4788998

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788998

COUNT(*)

----------

5132

SQL> /

输入 scn 的值: 4788999

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788999

COUNT(*)

----------

5132

SQL> /

输入 scn 的值: 4788572

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788572

COUNT(*)

----------

5132

SQL> /

输入 scn 的值: 4788200

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788200

COUNT(*)

----------

10072

SQL> /

输入 scn 的值: 4788300

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788300

COUNT(*)

----------

10072

SQL> /

输入 scn 的值: 4788400

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788400

COUNT(*)

----------

5132

SQL> /

输入 scn 的值: 4788350

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788350

COUNT(*)

----------

10072

SQL> insert into hjm_tb_test01_recov select * from hjm_tb_test01 as of scn 4788350;

已创建10072行。

SQL> commit;

提交完成。

SQL>