flashback (4)

jonelaw

4.对DML操作的闪回查询

Oracle10g对于闪回查询进行了增强，支持更简单的SQL操作，允许对误删除、误更新等DML操作进行闪回。关于9i的闪回查询请参考另一篇blog文章：
http://blog.chinaunix.net/u/10516/showart.php?id=482946

 

a.测试

1. SQL>conn u1/u1

1. SQL> create table t as select * from dba_users;

Table created.

 

1. SQL> select count(*) from dba_users;

  COUNT(*)
----------
        22

 

1. SQL> delete from t;

22 rows deleted.

 

1. SQL> select count(*) from t;

  COUNT(*)
----------
         0

 

1. SQL> select dbms_flashback.get_system_change_number from dual;

select dbms_flashback.get_system_change_number from dual
       *
ERROR at line 1:
ORA-00904: : invalid identifier

1. SQL> conn sys/sys as sysdba

Connected.

1. SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1388195

 

1. SQL> select count(*) from t as of scn 1388190;

select count(*) from t as of scn 1388190
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

 

1. SQL> conn u1/u1

Connected.

 

1. SQL> select count(*) from t as of scn 1388193;

  COUNT(*)
----------
         0

 

1. SQL> select count(*) from t as of scn 1388190;

  COUNT(*)
----------
        22

 

--在scn 1388190的时刻数据都还在。

 

1. SQL> insert into t select * from t as of scn 1388190;

22 rows created.

 

1. SQL> select count(*) from t;

  COUNT(*)
----------
        22