Oracle delete数据后恢复办法示例
Oracle delete数据后恢复办法示例
1 创建表
SQL>create table wdongh(
www.2cto.com
2 id integer,
3 name varchar2(60)
4 );
2 插入数据
SQL>insert into wdongh values(1,'wdh');
1 rowinserted
SQL>insert into wdongh values(2,'xiaoming');
1 rowinserted
SQL>insert into wdongh values(3,'hanmei');
www.2cto.com
1 rowinserted
SQL>insert into wdongh values(4,'leilei');
1 rowinserted
SQL>select * from wdongh;
ID NAME
----------- -----------
1 wdh
2 xiaoming
3 hanmei
4 leilei
3 删除数据
SQL>delete from wdongh;
4 rowsdeleted
SQL>commit;
Commitcomplete
SQL>select * from wdongh;
ID NAME
----------- -----------
4 获得当前SCN
Oracle 仅根据 SCN 执行恢复，它定义了数据库在某个确切时刻提交的版本。在事务提交时，它被赋予一个唯一的标示事物的SCN 。获得当前SCN的目的是：可以进行闪回查询尝试.
SQL>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
www.2cto.com
------------------------
668754
SQL>select count(*) from wdongh as of scn 668754;
COUNT(*)
----------
0
5 确定delete时的scn号
5.1 建立一个临时表用于存储在scn为多少的时候执行了delete
SQL>create table temp(count int,scn int);
Tablecreated
5.2 往临时表中加入数据
SQL>declare
2 iint :=668700;
3 begin
4 fori in 668700..668754 loop
5 insert into temp (scn) values (i);
6 update temp set count=(selectcount(*) from wdongh as of scn i) where scn=i;
7 endloop;
8 end;
9 /
PL/SQLprocedure successfully completed
www.2cto.com
SQL>commit;
Commitcomplete
5.3 查询scn为多少时执行了delete
SQL>select * from temp where count >0;
COUNT SCN
------------ -------------
4 668700
4 668701
4 668702
4 668703
4 668704
4 668705
4 668706
4 668707
8 rowsselected
SQL>select count(*) from wdongh as of scn 668707;
COUNT(*)
----------
4
SQL>select count(*) from wdongh as of scn 668708;
www.2cto.com
COUNT(*)
----------
0
我们看到在scn为668707时数据还在，即scn为668708就是我们delete的事务号。
6 恢复数据
SQL>insert into wdongh select * from wdongh as of scn 668707;
4 rowsinserted
SQL>select count(*) from wdongh;
COUNT(*)
----------
4
7 干掉临时表temp
SQL>drop table temp;
www.2cto.com
Tabledropped
SQL>commit;
Commitcomplete
作者 wdh226
Oracle delete数据后恢复办法示例 
