SQL>create table wdongh(
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');
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 获得当前SCNOracle 仅根据 SCN 执行恢复,它定义了数据库在某个确切时刻提交的版本。在事务提交时,它被赋予一个唯一的标示事物的SCN 。获得当前SCN的目的是:可以进行闪回查询尝试.
SQL>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
668754
SQL>select count(*) from wdongh as of scn 668754;
COUNT(*)
----------
0
5 确定delete时的scn号 5.1 建立一个临时表用于存储在scn为多少的时候执行了deleteSQL>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
SQL>commit;
Commitcomplete
5.3 查询scn为多少时执行了deleteSQL>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;
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 干掉临时表tempSQL>drop table temp;
Tabledropped
SQL>commit;
Commitcomplete
更多Oracle相关信息见 专题页面