flashback table实验（基于undo数据）

开启闪回：http://blog.csdn.net/rlhua/article/details/11934831

1、查询原数据

sys@TEST0910> select count(*) from test10;

 

  COUNT(*)

----------

        14

 

1 row selected.

2、查询数据库当前scn号，稍后作为恢复参考

sys@TEST0910> select checkpoint_change# from v$database;

 

CHECKPOINT_CHANGE#

------------------

           1952615

 

1 row selected.

3、误操作

sys@TEST0910> delete from scott.test10;

 

14 rows deleted.

 

sys@TEST0910> commit;

 

Commit complete.

 

4、根据之前的scn号使用undo数据闪回查询

sys@TEST0910> select * from scott.test10 as of scn 1952615;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

 

14 rows selected.

 

5、将数据还原

可以插入回去，如闪回查询那样： insert into test10 select * from scott.test10 as of scn 1952615;

另外一种，可以用闪回表的方式： flashback table scott.test10 to scn 1952615;

注意，闪回表时要开启 row movement，不然要报错。

 

sys@TEST0910> flashback table scott.test10 to scn 1952615;

flashback table scott.test10 to scn 1952615

                      *

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled

 

 

sys@TEST0910> alter table scott.test10 enable row movement;

 

Table altered.

 

sys@TEST0910> flashback table scott.test10 to scn 1952615;

 

Flashback complete.

 

sys@TEST0910> select count(*) from scott.test10;

 

  COUNT(*)

----------

        14

 

1 row selected.