eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 使用Oracle10g提供的flashback drop 新特性 | Blog首页 | Oracle10g的Flashback version Query »

使用Oracle10g的Flashback Query进行数据闪回

Oracle10g对于闪回查询进行了增强,支持更简单的SQL操作,允许对误删除、误更新等DML操作进行闪回。
看一下以下测试:
1.原表记录

$ sqlplus eygle/eygle SQL*Plus: Release 10.1.0.2.0 - Production on Wed Mar 30 08:52:04 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production With the Partitioning, OLAP and Data Mining options EYGLE on 30-MAR-05 >select count(*) from t1; COUNT(*) ---------- 9318

2.误删除所有记录
并且提交更改。

EYGLE on 30-MAR-05 >delete from t1; 9318 rows deleted. EYGLE on 30-MAR-05 >commit; Commit complete. EYGLE on 30-MAR-05 >select count(*) from t1; COUNT(*) ---------- 0

3.获得当前SCN
如果能够确切知道删除之前SCN最好,如果不知道,可以进行闪回查询尝试.

EYGLE on 30-MAR-05 >select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 10671006 EYGLE on 30-MAR-05 >select count(*) from t1 as of scn 10671000; COUNT(*) ---------- 0 EYGLE on 30-MAR-05 >select count(*) from t1 as of scn 10670000; COUNT(*) ---------- 9318

我们看到在SCN=10670000时,数据都在。
4.恢复数据.

EYGLE on 30-MAR-05 >insert into t1 select * from t1 as of scn 10670000; 9318 rows created. EYGLE on 30-MAR-05 >commit; Commit complete. EYGLE on 30-MAR-05 >select count(*) from t1; COUNT(*) ---------- 9318 EYGLE on 30-MAR-05 >



历史上的今天...
    >> 2017-03-30文章:
    >> 2012-03-30文章:
    >> 2011-03-30文章:
    >> 2009-03-30文章:
           关于ocssd进程的三言两语
    >> 2008-03-30文章:
           resize datafile 与 checkpoint
    >> 2006-03-30文章:
           广告: 招聘SQL SERVER DBA

无觅

By eygle on 2005-03-30 09:00 | Comments (1) | Backup&Recovery | 229 |

1 Comment

关于select * from table as of scn *******这个功能在9i也已经有了阿.


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com