注册
登录
| 关键词： nbsp wdongh SQL SCN count select 数据 insert delete rowinserted |
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 |
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
