flashback table

flashback table

1.注意点

flashback table和flashback query一样需要设置两个参数：

         UNDO_MANAGEMENT = AUTO

         undo_retention = 1800;   #这个时间可以随便设，他表示在系统中保留提交了的UNDO信息的时间，1800就是保留30分钟。

 

另外，还需要开启行移动

alter table table_name enable/disable row movement;

----查询该表是否开启row movement

select table_name,row_movement from user_tables where table_name='TABLENAME';

 

2.环境准备

sys@ORCL>conn shall/shall

Connected.

shall@ORCL>create table zhong1(id int,name varchar2(8));

Table created.

shall@ORCL>create table zhong2(id int,name varchar2(8));

Table created.

shall@ORCL>insert into zhong1 values(11,'oracle');

1 row created.

shall@ORCL>insert into zhong2 values(22,'mysql');

1 row created.

shall@ORCL>commit;

Commit complete.

 

3.记录当前时间，删除数据并提交

sys@ORCL>select dbms_flashback.get_system_change_number from dual;

sys@ORCL>select current_scn from v$database;

GET_SYSTEM_CHANGE_NUMBER

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

                 2701658

sys@ORCL>select sysdate from dual;

SYSDATE

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

2016-05-02 10:40:47

 

----删除数据并提交

shall@ORCL>delete zhong1;

1 row deleted.

shall@ORCL>delete zhong2;

1 row deleted.

shall@ORCL>commit;

Commit complete.

 

4.利用flashback table恢复表误操作数据

----表zhoang1开启行移动

shall@ORCL>alter table zhong1 enable row movement;

Table altered.

 

shall@ORCL>select table_name,row_movement from user_tables where table_name in ('ZHONG1','ZHONG2');

TABLE_NAME                     ROW_MOVE

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

ZHONG1                         ENABLED

ZHONG2                         DISABLED

 

----（1）恢复表zhong1数据

shall@ORCL>flashback table zhong1 to scn 2701658;

Flashback complete.

 

shall@ORCL>select * from zhong1;

        ID NAME

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

        11 oracle

 

----（2）恢复表zhong2数据

shall@ORCL>flashback table zhong2 to scn 2701658;

flashback table zhong2 to scn 2701658

                *

ERROR at line 1:

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

 

----不开启行移动恢复表zhong2方式（利用flashback query）

shall@ORCL>select * from zhong2;

no rows selected

 

shall@ORCL>select * from zhong2 as of scn 2701658;

        ID NAME

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

        22 mysql

 

shall@ORCL>create table zhong3 as select * from zhong2 as of scn 2701658;

Table created.

 

shall@ORCL>select * from zhong3;

        ID NAME

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

        22 mysql

 

----开启闪回恢复表zhong2方式

shall@ORCL>alter table zhong2 enable row movement;

Table altered.

 

shall@ORCL>flashback table zhong2 to scn 2701658;

Flashback complete.

 

shall@ORCL>select * from zhong2;

        ID NAME

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

        22 mysql

 

----利用flashback table比flashback query恢复数据更具优势，flashback table恢复表可以自动维护索引。

----查询索引状态： select INDEX_NAME, STATUS from user_indexes where table_name='TABLENAME';

 

 

----最后如果表误操作后，做了move或truncate表后，都不能闪回表了

shall@ORCL>truncate table zhong2;

Table truncated.

 

shall@ORCL>flashback table zhong2 to scn 2701658;

flashback table zhong2 to scn 2701658

                *

ERROR at line 1:

ORA-01466: unable to read data - table definition has changed

 

shall@ORCL>select * from zhong2 as of scn 2701658;

select * from zhong2 as of scn 2701658

              *

ERROR at line 1:

ORA-01466: unable to read data - table definition has changed

 

 

----另外需要注意的是

首先sys用户不支持flashback table，但是使用system表空间的普通用户是支持的

--------->system表空间中其他用户测试

sys@ORCL>create user tt identified by tt default tablespace system;

sys@ORCL>grant connect,resource to tt;

tt@ORCL>create table zhong1(zhong int);

tt@ORCL>insert into zhong1 values(111);

tt@ORCL>commit;

tt@ORCL>select current_scn from v$database;

tt@ORCL>delete zhong1;

tt@ORCL>commit;

tt@ORCL>alter table zhong1 enable row movement;

tt@ORCL>flashback table zhong1 to scn 2709357;

tt@ORCL>select * from zhong1;

     ZHONG

----------

       111

 

---------->sys用户测试

sys@ORCL>select current_scn from v$database;

CURRENT_SCN

-----------

    2709805

sys@ORCL>delete zhong1;

1 row deleted.

sys@ORCL>commit;

Commit complete.

sys@ORCL>alter table zhong1 enable row movement;

Table altered.

sys@ORCL>flashback table zhong1 to scn 2709805;

flashback table zhong1 to scn 2709805

                *

ERROR at line 1:

ORA-08185: Flashback not supported for user SYS

sys@ORCL>select * from zhong1 as of scn 2709805;

         X

----------

        22

 

 

 

 

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

转载于:http://blog.itpub.net/30130773/viewspace-2102640/