Oracle Flashback特性(二)



Flashback Table

Flashback Table不仅恢复过去一个时间点的表的行数据, 数据库在线时同样也恢复表的索引, 触发器和约束, 增加数据库的整体可用性. 表使用时间点或者SCN进行恢复. 在用户小范围错误或极少数几个表的情况下, Flashback Table比其它的闪回方法更有效. 对于恢复大量量, Flashback Database是个更好的选择. Flashback Table不能用于备用数据库, 不能重建所有的DDL操作, 如增加和删除字段.

在使用Flashback Table操作表之前必须在表上启用行移动功能(row movement). Oracle段收缩功能也需要行移动功能支持.

SQL> delete from flashtest;

7 rows deleted

SQL> commit;

Commit complete

SQL> flashback table flashtest
  2  to timestamp systimestamp – interval ’15′ minute;

flashback table flashtest
to timestamp systimestamp – interval ’15′ minute

ORA-08189: 因为未启用行移动功能, 不能闪回表

SQL> alter table flashtest enable row movement;

Table altered

SQL> delete from flashtest;

7 rows deleted

SQL> commit;

Commit complete

SQL> flashback table flashtest
  2  to timestamp systimestamp – interval ’15′ minute;

Done

SQL> select count(*) from flashtest;

  COUNT(*)
———-
         7

Flashback Version Query

Flashback Version Query是另一个依赖重做数据的闪回特性, 提供比as of查询更深层次的细节描述, 返回给定期间或SCN内的所有历史操作记录.

Flashback Version Query使用versions between子句为分析的表指定SCN和期间范围, 并利用一些虚拟列确定改变的SCN和时期, 事务ID和操作类型.

虚拟列说明如下:

VERSIONS_START{SCN|TIME} : 当行版本被改变时的开始SCN和时间戳, 这里行已改变.
VERSION_END{SCN|TIME} : 改变对行不再有效时的结束SCN和时间戳, 若该参数为空, 则行的版本是当前的, 或已经被删除.
VERSIONS_XID : 为行版本创建的事务ID
VERSIONS_OPERATION : 行上执行的操作(I, U, D)

具体见下面实验:

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
                 3002569

SQL> update flashtest set object_id=100
  2  where object_id=46;

1 row updated

SQL> delete from flashtest where object_id=’15′;

1 row deleted

SQL> insert into flashtest
  2  select * from all_objects
  3  where rownum<2;

1 row inserted

SQL> update flashtest set object_id=200 where object_id= 3;

1 row updated

SQL> commit;

Commit complete

SQL> update flashtest set object_id = 300 where object_id = 54;

1 row updated

SQL> commit;

Commit complete

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
                 3005474

SQL> select versions_startscn startscn, versions_endscn endscn,
  2  versions_xid xid, versions_operation oper,
  3  object_id, object_name
  4  from flashtest
  5  versions between scn 3002569 and 3005474;

  STARTSCN   ENDSCN XID                           OPER  OBJECT_ID OBJECT_NAME
—————- ———- ————————– ——- ———- ——————
   3005473                 05001400A8070000  U         300       I_CDEF2
   3005406                 090015006D070000  U         200       I_OBJ#
   3005406                 090015006D070000  I             20       ICOL$
   3005406                 090015006D070000  D           15       UNDO$
   3005406                 090015006D070000  U         100       I_USER1
                    3005406                                                     46        I_USER1
                                                                                         28        CON$
                    3005406                                                    15        UNDO$
                                                                                        29        C_COBJ#
                    3005406                                                      3         I_OBJ#
                                                                                        25        PROXY_ROLE_DATA$
                    3005473       &n
bsp;                                            54        I_CDEF2

12 rows selected