flashback table与索引、约束

最新推荐文章于 2017-01-12 21:54:34 发布

cqlilinfeng2009

最新推荐文章于 2017-01-12 21:54:34 发布

阅读量176

点赞数

文章标签：数据库

flashback table实际上是用undo表空间中存储的undo信息来rollback表,在表上逻辑地执行DML语句来做回滚.
1、索引
由于flashback table实际上是执行DML语句来rollback表，因此flashback过程中oracle只会维护当前存在的索引.对于过去曾经存在的索引是无法恢复的.
在flashback table时，如果当前有索引，则flashback table后仍然有该索引;如果当前没有索引,flashback table后也没有索引，不管过去是否存在过该索引.
flashback table后需要考虑一下是否需要增加索引或删除索引.
对于索引而言,有四种情况：(1)过去有，现在没有　(2)过去有，现在有　(3)过去没有，现在没有　(4)过去没有，现在有
现在只测试情况(1)、(2)和(4)
测试过程：
SQL> create table p1 (id number);

Table created.

SQL> create table p2(id number);

Table created.

SQL> create table p4 (id number);

Table created.

SQL> create index idx_p1 on p1(id);

Index created.

SQL> create index idx_p2 on p2(id);

Index created.

SQL> insert into p1 values (1);

1 row created.

SQL> insert into p2 values (2);

1 row created.

SQL> insert into p4 values (4);

1 row created.

SQL> commit;

Commit complete.

SQL> select index_name,index_type,table_name,status from user_indexes where table_name in ('P1','P2','P4');

INDEX_NAME INDEX_TYPE TABLE_NAME STATUS
---------- ---------- ---------- ----------
IDX_P2 NORMAL P2 VALID
IDX_P1 NORMAL P1 VALID

SQL> select * from p1;

ID
----------
1

SQL> select * from p2;

ID
----------
2

SQL> select * from p4;

ID
----------
4

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
8057220380

当前状态是p1和p2有索引，p4没有索引.

SQL> create index idx_p4 on p4(id);

Index created.

SQL> drop index IDX_P1;

Index dropped.

SQL> insert into p1 values (10);

1 row created.

SQL> insert into p2 values (20);

1 row created.

SQL> insert into p4 values (40);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from p1;

        ID
----------
         1
        10

SQL> select * from p2;

        ID
----------
         2
        20

SQL> select * from p4;

        ID
----------
         4
        40

SQL> select index_name,index_type,table_name,status from user_indexes where table_name in ('P1','P2','P4');

INDEX_NAME INDEX_TYPE TABLE_NAME STATUS
---------- ---------- ---------- ----------
IDX_P4 NORMAL P4 VALID
IDX_P2 NORMAL P2 VALID

当前P2与P4有索引，p1上的索引已经删除。

SQL> alter table p1 enable row movement;

Table altered.

SQL> alter table p2 enable row movement;

Table altered.

SQL> alter table p4 enable row movement;

Table altered.

flashback table p1,p2,p4 to scn 8057220380;

SQL> flashback table p1,p2,p4 to scn 8057220380;

Flashback complete.

SQL> select * from p1;

ID
----------
1

SQL> select * from p2;

ID
----------
2

SQL> select * from p4;

ID
----------
4
三个表都已经flashback成功.

SQL> select index_name,index_type,table_name,status from user_indexes where table_name in ('P1','P2','P4');

INDEX_NAME INDEX_TYPE TABLE_NAME STATUS
---------- ---------- ---------- ----------
IDX_P4 NORMAL P4 VALID
IDX_P2 NORMAL P2 VALID

flashback table后p4上后加的索引仍然存在，p1上的索引并没有恢复，p2则一直有索引.

2、约束
flashback table无法将表恢复到add constaint之前的某个时刻，但可以恢复到drop constraint之前的某个时刻(flashback table无法恢复已经删除的约束).
对于约束而言,有四种情况：(1)过去有，现在没有　(2)过去有，现在有　(3)过去没有，现在没有　(4)过去没有，现在有
现在只测试情况(1)、(2)和(4)
SQL> create table p1 (x number,y varchar2(10) constraint cons_p1_nn not null);

Table created.

SQL> create table p2 (x number,y varchar2(10) constraint cons_p2_nn not null);

Table created.

SQL> create table p4 (x number,y varchar2(10));

Table created.

SQL> insert into p1 values (1,'test1');

1 row created.

SQL> insert into p2 values (2,'test2');

1 row created.

SQL> insert into p4 values (4,null);

1 row created.

SQL> commit;

SQL> select * from p1;

         X Y
---------- --------------------
         1 test1

SQL> select * from p2;

         X Y
---------- --------------------
         2 test2

SQL> select * from p4;

         X Y
---------- --------------------
         4

SQL> select constraint_name,constraint_type,table_name,status,validated,invalid from user_constraints where table_name in ('P1','P2','P4');

CONSTRAINT_NAME CO TABLE_NAME STATUS     VALIDATED INVALID
--------------- -- ---------- ---------- ---------- --------------
CONS_P1_NN      C P1         ENABLED    VALIDATED
CONS_P2_NN      C P2         ENABLED    VALIDATED
p1和p2上有约束.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
8082593986

SQL> alter table p1 drop constraint CONS_P1_NN;

Table altered.

SQL> alter table p4 modify (y constraint cons_p4_nn not null);
alter table p4 modify (y constraint cons_p4_nn not null)
                                    *
ERROR at line 1:
ORA-02296: cannot enable (USER1.CONS_P4_NN) - null values found
表中有记录不符合约束条件，修改记录

SQL> update p4 set y='test4';

1 row updated.

SQL> commit;

Commit complete.

SQL> alter table p4 modify (y constraint cons_p4_nn not null);

Table altered.

插入数据：
SQL> insert into p1 values (10,null);

1 row created.

SQL> insert into p2 values (20,'test20');

1 row created.

SQL> insert into p4 values (40,'test40');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from p1;

         X Y
---------- --------------------
         1 test1
        10

SQL> select * from p2;

         X Y
---------- --------------------
         2 test2
        20 test20

SQL> select * from p4;

         X Y
---------- --------------------
         4 test4
        40 test40

SQL> select constraint_name,constraint_type,table_name,status,validated,invalid from user_constraints where table_name in ('P1','P2','P4');

CONSTRAINT_NAME CO TABLE_NAME STATUS     VALIDATED INVALID
--------------- -- ---------- ---------- ---------- --------------
CONS_P2_NN      C P2         ENABLED    VALIDATED
CONS_P4_NN      C P4         ENABLED    VALIDATED
p2和p4上有约束.

SQL> alter table p1 enable row movement;

Table altered.

SQL> alter table p2 enable row movement;

Table altered.

SQL> alter table p4 enable row movement;

Table altered.

SQL> flashback table p1 to scn 8082593986;

Flashback complete.

SQL> select * from p1;

X Y
---------- --------------------
1 test1
p1表的flashback已经跨越了drop constraint.

SQL> flashback table p2 to scn 8082593986;

Flashback complete.

SQL> select * from p2;

         X Y
---------- --------------------
         2 test2

SQL> flashback table p4 to scn 8082593986;
flashback table p4 to scn 8082593986
                *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
不能flashback，提示表定义已经更改.

SQL> alter table p4 drop constraint cons_p4_nn;

Table altered.

SQL> flashback table p4 to scn 8082593986;
flashback table p4 to scn 8082593986
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
删除约束后也不能flashback table.因为表的定义改变了.
说明flashback table无法跨越add constraint恢复表.

SQL> select constraint_name,constraint_type,table_name,status,validated,invalid from user_constraints where table_name in ('P1','P2','P4');

CONSTRAINT_NAME CO TABLE_NAME STATUS VALIDATED INVALID
--------------- -- ---------- ---------- ---------- --------------
CONS_P2_NN C P2 ENABLED VALIDATED
flashback table后p1表上的约束并未恢复.

再用check constraint测试一下跨越add constraint的问题:
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
8082595262

SQL> select * from p4;

         X Y
---------- --------------------
         4 test4
        40 test40

SQL> delete from p4 where x=4;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from p4;

X Y
---------- --------------------
40 test40
删除x<10的记录

SQL> alter table p4 add constraint cons_check_x check(x>10);

Table altered.

SQL> insert into p4 values (400,'test400');

1 row created.

SQL> commit;

Commit complete.

SQL> select constraint_name,constraint_type,table_name,status,validated,invalid from user_constraints where table_name='P4';

CONSTRAINT_NAME CO TABLE_NAME STATUS VALIDATED INVALID
--------------- -- ---------- ---------- ---------- --------------
CONS_CHECK_X C P4 ENABLED VALIDATED

SQL> select * from p4;

         X Y
---------- --------------------
        40 test40
       400 test400

SQL> flashback table p4 to scn 8082595262;
flashback table p4 to scn 8082595262
                *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

SQL> alter table p4 drop constraints CONS_CHECK_X;

Table altered.

SQL> flashback table p4 to scn 8082595262;
flashback table p4 to scn 8082595262
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

check constraint测试结果同样表明flashback table不能跨越add constraint.

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

转载于:http://blog.itpub.net/228190/viewspace-1446/