闪回技术是恢复技术的革新性进步，恢复速度快且易于操作，闪回操作有如下功能：
oracle10g：闪回表、闪回删除、闪回查询、闪回版本、闪回事务、闪回数据库
oracle11g：闪回数据归档
下面做一下闪回表实践
一、语法
FLASHBACK TABLE [schema.]table[,[schema.]table ]...
TO { SCN | TIMESTAMP } expr [ { ENABLE | DISABLE } TRIGGERS ]
从语法中我们可以看出，闪回的动作可以依托于SCN或时间，同时，语法中还有触发器相关子句
二、创建测试表
1:包括：索引、触发器
SCOTT@ORA10G>create table fb_table(x number(2),d date);
Table created.
SCOTT@ORA10G>create or replace trigger trg_fb_table
2 before insert on fb_table for each row
3 begin
4 if :new.d is null then
5 :new.d := to_date('20080808','yyyymmdd');
6 end if;
7 end;
8 /
Trigger created.
SCOTT@ORA10G>create unique index idx_fb_table on fb_table(x);
Index created.
SCOTT@ORA10G>
2:查看表fb_table及相关对象的状态
SCOTT@ORA10G>SELECT object_name,status FROM user_objects WHERE object_name like'%FB_TABLE';
OBJECT_NAME STATUS
--------------- ------
FB_TABLE VALID
IDX_FB_TABLE VALID
TRG_FB_TABLE VALID
SCOTT@ORA10G>
2:插入测试数据验证触发器
SCOTT@ORA10G>insert into fb_table(x) values(1);
1 row created.
SCOTT@ORA10G>insert into fb_table(x,d) values(2,sysdate);
1 row created.
SCOTT@ORA10G>commit;
Commit complete.
SCOTT@ORA10G>select * from fb_table;
X D
--------- -------------------
1 2008-08-08 00:00:00
2 2012-10-08 09:03:05
SCOTT@ORA10G>
三、查看系统时间、删除x=2记录
SCOTT@ORA10G>select sysdate from dual;
SYSDATE
-------------------
2012-10-08 09:03:34
SCOTT@ORA10G>delete fb_table where x=2;
1 row deleted.
SCOTT@ORA10G>commit;
Commit complete.
SCOTT@ORA10G>select * from fb_table;
X D
--------- -------------------
1 2008-08-08 00:00:00
SCOTT@ORA10G>
四、闪回指定时间点
SCOTT@ORA10G>flashback table fb_table to TIMESTAMP to_date('2012-10-08 09:03:34','yyyy-mm-dd hh24:mi:ss');
flashback table fb_table to TIMESTAMP to_date('2012-10-08 09:03:34','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
SCOTT@ORA10G>
错误为表不允许行移动，闪回表的前提就是要求表必须允许行移动，启用行移动
SCOTT@ORA10G>select table_name, row_movement from user_tables where table_name='FB_TABLE';
TABLE_NAME ROW_MOVE
-------------------------- --------
FB_TABLE DISABLED
SCOTT@ORA10G>alter table fb_table enable row movement;
Table altered.
SCOTT@ORA10G>select table_name, row_movement from user_tables where table_name='FB_TABLE';
TABLE_NAME ROW_MOVE
-------------------------- --------
FB_TABLE ENABLED
SCOTT@ORA10G>
五、再次闪回指定时间点
SCOTT@ORA10G>flashback table fb_table to TIMESTAMP to_date('2012-10-08 09:03:34','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SCOTT@ORA10G>select * from fb_table;
X D
--------- -------------------
1 2008-08-08 00:00:00
2 2012-10-08 09:03:05
SCOTT@ORA10G>SELECT object_name,status FROM user_objects WHERE object_name like'%FB_TABLE';
OBJECT_NAME STATUS
--------------- -------
FB_TABLE VALID
IDX_FB_TABLE VALID
TRG_FB_TABLE INVALID
SCOTT@ORA10G>
至此，闪回已经成功，不过，触发器（TRG_FB_TABLE）却失效了，也就是说，在闪回时，默认情况下是disable triggers的。我们可以重新编译TRG_FB_TABLE（alter trigger trg_fb_table compile），或者我们闪回的时候直接指定enable triggers，如下：
SCOTT@ORA10G>flashback table fb_table to TIMESTAMP to_date('2012-11-15 09:03:34','yyyy-mm-dd hh24:mi:ss') enable triggers;
Flashback complete.
SCOTT@ORA10G>select * from fb_table;
X D
---------- -------------------
1 2008-08-08 00:00:00
2 2012-11-15 09:03:05
SCOTT@ORA10G>SELECT object_name,status FROM user_objects WHERE object_name like'%FB_TABLE';
OBJECT_NAME STATUS
--------------- ------
FB_TABLE VALID
IDX_FB_TABLE VALID
TRG_FB_TABLE VALID
SCOTT@ORA10G>
六：其他
1:按照时间闪回，如：闪回到5分钟前，也可以用如下方式完成
SCOTT@ORA10G>flashback table fb_table to TIMESTAMP sysdate - 5/1440;
Flashback complete.
SCOTT@ORA10G>
或
SCOTT@ORA10G>flashback table fb_table to TIMESTAMP sysdate - interval '5' minute;
Flashback complete.
SCOTT@ORA10G>
2:闪回到指定SCN
SCOTT@ORA10G>flashback table fb_table to scn 553600;
Flashback complete.
SCOTT@ORA10G>
3:同时闪回多张表到指定SCN
SCOTT@ORA10G>flashback table fb_table,dept to scn 553600;
Flashback complete.
SCOTT@ORA10G>
4:闪回表为DDL操作，默认提交，如下测试：
SCOTT@ORA10G>select * from fb_table;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@ORA10G>delete fb_table where deptno>=20;
3 rows deleted.
SCOTT@ORA10G>flashback table dept to scn 553600;
Flashback complete.
SCOTT@ORA10G>rollback;
Rollback complete.
SCOTT@ORA10G>select * from fb_table;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SCOTT@ORA10G>
经过这个测试，我们会发现，闪回表以后，delete操作已经做了commit操作。
七：小结
1:闪回表是根据undo数据来完成的，所以，能否闪回或者说闪回多长时间的数据，那么要考虑undo_retention和retention guarantee；
2:闪回表优势也很大，速度很快易于操作；
3:闪回表可以基于时间或者SCN
4:闪回表必须启用允许行移动
5:默认情况下，闪回表后，触发器为失效状态，解决办法：重新编译触发器或者闪回表的时候指定enable triggers；
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/685769/viewspace-749324/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
【闪回】删除
请登录后发表评论
登录
全部评论
