ORACLE—DELETE表后的恢复

最新推荐文章于 2025-12-03 13:04:04 发布

转载最新推荐文章于 2025-12-03 13:04:04 发布 · 533 阅读

文章标签：

#Oracle数据库 #数据库 #行业数据 #Oracle

oracle 专栏收录该内容

4 篇文章

订阅专栏

本文演示了在Oracle数据库中如何创建表、插入数据、误删除数据后的SCN获取及通过SCN恢复数据的过程。

一）连接到数据库：
C:/Documents and Settings/Administrator>sqlplus system/sa@ot
SQL *Plus: Release 9.0.1.0.1 - Production on Wed May 13 19:13:53 2009
(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
（二）创建表
SQL> create table t (id int );
Table created.
SQL>
（三）插入10000条数据
SQL> declare
  2 i int :=1;
  3  begin
  4  for i in 1..10000 loop
  5 insert into t values(i);
  6  end loop;
  7  end ;
  8  /
PL/SQL procedure successfully completed.
SQL> select count(*) from t;
  COUNT(*)
----------
   10000
SQL> commit;
Commit complete.
SQL>
（四）误删除所有记录，并且提交更改。
SQL> select count(*) from t;
  COUNT(*)
----------
   10000
SQL> delete from t;
10000 rows deleted.
SQL> select count(*) from t;
  COUNT(*)
----------
      0
SQL> rollback;
Rollback complete.
SQL> select count(*) from t;
  COUNT(*)
----------
   10000
SQL> delete from t;
10000 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from t;
  COUNT(*)
----------
      0
SQL> rollback;
Rollback complete.
SQL> select count(*) from t;
  COUNT(*)
----------
      0
SQL>

（四）获得当前SCN（Oracle 仅根据 SCN 执行恢复，它定义了数据库在某个确切时刻提交的版本。在事务提交时，它被赋予一个唯一的标示事物的SCN ）获得当前SCN的目的是：可以进行闪回查询尝试.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
               961371

SQL>  select count(*) from t as of scn 9611370;
  COUNT(*)
----------
         0

（五）确定delete 时候的scn号

[1]建立一个临时表用于存储在scn为多少的时候执行了delete

create table temp(count int ,scn int);
[2]往临时表中加入数据
declare
i int :=961000;
begin
for i in 961000..961371 loop
  insert into temp (scn) values (i);
  update  temp set count=(select count(*) from t as of scn i) where scn=i;
end loop;
end ;
/
[3]查询scn为多少的时候执行了delete
SQL> select  * from temp where count >0;

   COUNT       SCN
---------- ----------
   10000    961147
   10000    961148
   10000    961149
   10000    961150
   10000    961151
   10000    961152
   10000    961153
   10000    961154
   10000    961155
   10000    961156
   10000    961157
   10000    961158
   10000    961159
   10000    961160
   10000    961161
   10000    961162

SQL>  select count(*) from t as of scn 961162;
  COUNT(*)
----------
   10000
SQL>  select count(*) from t as of scn 961163;
  COUNT(*)
----------
      0
我们看到在SCN=961162时数据都在。即scn为961163就是我们DELETE的事务号。

(六)恢复数据

SQL> insert into t select * from t as of scn 961162;
10000 rows created.

SQL> select count(*) from t;
  COUNT(*)
----------
   10000
SQL>

(七)清理temp表。
SQL> drop table temp;
Table dropped.

SQL> commit;
Commit complete.

SQL> select count(*) from t;
  COUNT(*)
----------
   10000

（八）总结
DELETE的事务号也可以用logminer来方便的获得。系列实验待续……