[bbk4759] 第28集 - 第三章 Flashback Table 05

实现Flashback table操作的前提条件

权限及及开启movement row功能

/*************************************************************************/

实验:flashback table操作

1、emp1-7369-900

2、emp1-7369-900-1000

     error where deptno=20---7369-1900

3、flashback version query

4、flashback table---recover

总结:

　　1、比incomplete recovery 影响返回小，保证数据库online

　　2、当表的子集发生错误操作时，可以通过flashback table功能实现.

/*************************************************************************/

SQL> create table emp1 as select * from emp;

Table created.

SQL> select empno,sal,deptno from emp1 where deptno = 20;

     EMPNO        SAL     DEPTNO
---------- ---------- ----------
      7369        800         20
      7566       2975         20
      7788       3000         20
      7876       1100         20
      7902       3000         20

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') current_date from dual;

CURRENT_DATE
-------------------
2013-05-17 07:43:04

SQL> update emp1 set sal = 1900 where deptno = 20;

5 rows updated.

SQL> commit;

Commit complete.

step 3 :使用flashback version query,定位数据变化.

SQL> l
  1  select versions_xid,versions_starttime,versions_endtime,empno,sal
  2  from emp1
  3  versions between timestamp
  4     to_timestamp('2013-05-17 07:43:04','yyyy-mm-dd hh24:mi:ss')
  5     and maxvalue
  6* where empno = 7369
SQL> /

VERSIONS_XID     VERSIONS_STARTTIME        VERSIONS_ENDTIME               EMPNO        SAL
---------------- ------------------------- ------------------------- ---------- ----------
02001000EE080000 17-MAY-13 07.55.05 AM                                     7369       1900
                                           17-MAY-13 07.55.05 AM           7369        800

开启emp1表的行移动功能

SQL> alter table emp1 enable row movement;

Table altered.

闪回emp1表

SQL> flashback table emp1 to timestamp
  2   to_timestamp('2013-05-17 07:43:04','yyyy-mm-dd  hh24:mi:ss');

Flashback complete.

验证数据

SQL> select empno,sal,deptno from emp1
  2   where deptno = 20;

     EMPNO        SAL     DEPTNO
---------- ---------- ----------
      7369        800         20
      7566       2975         20
      7788       3000         20
      7876       1100         20
      7902       3000         20

Flashback Table:Considerations

• The FLASHBACK TABLE command executes as a single transaction,acquiring exclusive DML locks.
  • Flashback Table操作相当于一个DML操作，独立的锁机制；要么都成功，要么都失败
• Statistics are not flashed back.
  • 执行Flashback table之后，数据字典中相应的statistics data不会发生变化.
• Current indexes and dependent objects are maintained
  • Oracle database在执行Flashback table后，会自动维护相应的索引及对象信息;
  • 比如与表对应的materialized views数据,oracle database也会自动进行维护，与变化后的数据保持一致.
• Flashback Table operations
• Cannot be performed on system tables
  • 假如在system用户下，建了业务表，这种表也是不能进行闪回操作的.
• Cannot span DDL operations
• Generate undo and redo data