【备份恢复】 闪回技术之闪回表

1.创建测试表 fb_table,在表 fb_table 上创建触发器（ trg_fb_table）， fb_table 的 x 字段创建一个索引（ idx_fb_table），
此时，与 fb_table 相关的对象就有两个，一个是触发器，一个是索引，这三个对象的状态都是有效的。

SCOTT@PROD1>ed 001
create or replace trigger trg_fb_table before insert on fb_table for each row
begin
if :new.d is null then
:new.d :=to_date('20080808','yyyymmdd');
end if;
end;
/

SCOTT@PROD1>@001

Trigger created.

SCOTT@PROD1>create unique index idx_fb_table on fb_table(x);

Index created.

SCOTT@PROD1>col object_name for a20;
SCOTT@PROD1>select object_name,status from user_objects where object_name like '%FB_TABLE';

OBJECT_NAME          STATUS
-------------------- -------
TRG_FB_TABLE         VALID
IDX_FB_TABLE         VALID
FB_TABLE             VALID

插入测试数据，验证触发器
SCOTT@PROD1>insert into fb_table(x) values(1);

1 row created.

SCOTT@PROD1>insert into fb_table(x,d) values(2,sysdate);

1 row created.

SCOTT@PROD1>commit;

Commit complete.

SCOTT@PROD1>select * from fb_table;

        X D
---------- -------------------
        1 2008-08-08 00:00:00
        2 2016-12-23 09:51:11

       
记录当前系统时间
SCOTT@PROD1>select sysdate from dual;

SYSDATE
-------------------
2016-12-23 09:51:47

SCOTT@PROD1>delete fb_table where x=2;

1 row deleted.

SCOTT@PROD1>commit;

Commit complete.

SCOTT@PROD1>select * from fb_table;

        X D
---------- -------------------
        1 2008-08-08 00:00:00

尝试通过闪回表的方式恢复数据，第一次闪回表报错很明显，没有启用行移动，启用后，成功闪回表
SCOTT@PROD1>flashback table fb_table to timestamp to_date('2016-12-23 09:51:47','yyyy-mm-dd hh24:mi:ss');
flashback table fb_table to timestamp to_date('2016-12-23 09:51:47','yyyy-mm-dd hh24:mi:ss')
               *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

查询表fb_table是否启动了行移动
SCOTT@PROD1>select table_name,row_movement from user_tables where table_name='FB_TABLE';

TABLE_NAME                     ROW_MOVE
------------------------------ --------
FB_TABLE                       DISABLED

SCOTT@PROD1>alter table fb_table enable row movement;

Table altered.

SCOTT@PROD1>select table_name,row_movement from user_tables where table_name='FB_TABLE';

TABLE_NAME                     ROW_MOVE
------------------------------ --------
FB_TABLE                       ENABLED


再次执行闪回操作
SCOTT@PROD1>flashback table fb_table to timestamp to_date('2016-12-23 09:51:47','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SCOTT@PROD1>select * from fb_table;

        X D
---------- -------------------
        1 2008-08-08 00:00:00
        2 2016-12-23 09:51:11

查看与测试表相关对象
所有对象的状态均为有效的，看来，测试的时候我们创建触发器和索引意义不大了，不过要说明的是，
11g 在闪回表上做了升级， 10g 闪回表后，触发器为失效的状态
SCOTT@PROD1>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

2.当然，我们也可以通过以下方式进行闪回表
SCOTT@PROD1>flashback table fb_table to timestamp sysdate - 15/1440;

Flashback complete.

SCOTT@PROD1>select * from fb_table;

        X D
---------- -------------------
        1 2008-08-08 00:00:00
        2 2016-12-23 09:51:11

SCOTT@PROD1>flashback table fb_table to TIMESTAMP sysdate - interval '15' minute;

Flashback complete.

SCOTT@PROD1>select * from fb_table;

        X D
---------- -------------------
        1 2008-08-08 00:00:00
        2 2016-12-23 09:51:11

3.单张表基于SCN闪回表
SCOTT@PROD1>insert into fb_table(x,d) values(3,sysdate);

1 row created.

SCOTT@PROD1>commit;

Commit complete.

SCOTT@PROD1>select * from fb_table;

        X D
---------- -------------------
        1 2008-08-08 00:00:00
        2 2016-12-23 09:51:11
        3 2016-12-23 10:11:09

SCOTT@PROD1>select dbms_flashback.get_system_change_number as scn from dual;

      SCN
----------
  1191441

SCOTT@PROD1>delete from fb_table where x=3;

1 row deleted.

SCOTT@PROD1>commit;

Commit complete.

SCOTT@PROD1>flashback table fb_table to scn 1191441;

Flashback complete.

SCOTT@PROD1>select * from fb_table;

        X D
---------- -------------------
        1 2008-08-08 00:00:00
        2 2016-12-23 09:51:11
        3 2016-12-23 10:11:09

多张表基于SCN闪回表
SCOTT@PROD1>alter table dept enable row movement;    

Table altered.

SCOTT@PROD1>flashback table fb_table,dept to scn 1191441;

Flashback complete.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31400681/viewspace-2131259/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31400681/viewspace-2131259/