ITPub博客

flashback table与索引、约束

原创 Linux操作系统 作者:cqubityj 时间:2007-12-26 12:01:51 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-19

  • 博文量
    133
  • 访问量
    423291