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/，如需转载，请注明出处，否则将追究法律责任。
最新文章
- HOWTO Copy files using cp, cpio, dd and tar
- 关于PLOGI failed
- Linux Hardware RAID Howto
- how to align lun to RAID strip size on linux
- veritas扩展卷和文件系统
- RMAN__ catalog archivelog for a directory
- insert语句与gc cr multi block request等待
- oracle10g RAC配置VIP IPMP
- Dynamic Resource Mastering (ZT)
- 关于library cache lock及library cache pin
