不小心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>