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

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

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

作者：eygle |【转载时请务必以超链接形式标明文章原始出处和作者信息及本声明】
链接：http://www.eygle.com/archives/2005/03/eoaoracle10gaef.html

站内相关文章|Related Articles

Oracle11g的新特性:Database和SQL重演(replay)

修改默认的undo_retention参数设置

How to maintain Oracle10g Recyclebin?

Oracle10g New Feature:Flash Recovery Area Space Management

Oracle 10gR2 Study Notes:New View V$FLASH_RECOVERY_AREA_USAGE

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 >

-----
这篇【使用Oracle10g的Flashback Query进行数据闪回】来自 www.eygle.com | CSDN技术网摘| del.icio.us|365Key

By eygle on 2005-03-30 09:00 | Comments (1) | Posted to Backup&Recovery | Edit |Pageviews:

相关文章	随机文章
Oracle11g的新特性:Database和SQL重演(replay) 修改默认的undo_retention参数设置 How to maintain Oracle10g Recyclebin? Oracle10g New Feature:Flash Recovery Area Space Management Oracle 10gR2 Study Notes:New View V$FLASH_RECOVERY_AREA_USAGE	世界是平的网络是脆弱的 Oracle经典图书之-Oracle8i Internal Services 字符集问题的初步探讨(二)-数据库的字符集 HP-Unix中的时区设置《循序渐进Oracle》一书出版

网上相关主题:

Listed below are links to weblogs that reference 使用Oracle10g的Flashback Query进行数据闪回:

» what is SMON_SCN_TIME ? from Friends Life and Oracle
SMON_SCN_TIME是Oracle数据库的系统表，用以进行辅助恢复等功能。在Oracle9iR2中,SMON_SCN_TIME每5分钟被更新一次。 [Read More]

Tracked on September 7, 2006 1:54 PM

留言 (1)

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

Posted by: Steven at September 1, 2006 8:18 PM

www.eygle.com

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

留言 (1)

发表留言: