3.2.7.2 (分区表enable)—(非分区表diable keep index)—exchange--exchange
解释:
首先:分区表primary key是enable的,非分区表primary key是disable的;
其次:交换分区且包括including indexes;
然后:再次交换分区包括including indexes;
SQL> alter table t_no_par disable constraint ind_t_no_par keep index;
表已更改。
--此时将非分区表的primary key diable掉,然后指定keep index
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';
INDEX_NAMEPARTITION_NAMESTATUS
------------------------------ ------------------------------ --------
IND_T_PAR_RANGEP_T_PAR_RANGE_0USABLE
IND_T_PAR_RANGEP_T_PAR_RANGE_1USABLE
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE index_NAME in ('IND_T_NO_PAR');
INDEX_NAMEINDEX_TYPESTATUS
------------------------------ --------------------------- --------
IND_T_NO_PARNORMALVALID
--索引仍然有效
SQL> select constraint_name,constraint_type,status,validated from User_Constraints where constraint_name in ('IND_T_NO_PAR','IND_T_PAR_RANGE');
CONSTRAINT_NAMEC STATUSVALIDATED
------------------------------ - -------- -------------
IND_T_PAR_RANGEP ENABLEDVALIDATED
IND_T_NO_PARP DISABLED NOT VALIDATED
--约束已经disable了,且not validated
SQL> select * from t_no_par;
未选定行
SQL> select * from t_par_range;
TIMEIDCITY_IDVALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-3月-111101 a
01-4月-112202 b
SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes;
表已更改。
--将分区表与非分区表做第一次交换,注意执行此命令前,各自约束和索引的状态
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE index_NAME in ('IND_T_NO_PAR');
INDEX_NAMEINDEX_TYPESTATUS
------------------------------ --------------------------- --------
IND_T_NO_PARNORMALVALID
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';
INDEX_NAMEPARTITION_NAMESTATUS
------------------------------ ------------------------------ --------
IND_T_PAR_RANGEP_T_PAR_RANGE_0USABLE
IND_T_PAR_RANGEP_T_PAR_RANGE_1USABLE
--交换之后,各自约束和索引的状态
SQL> select * from t_no_par;
TIMEIDCITY_IDVALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-3月-111101 a
SQL> select * from t_par_range;
TIMEID CITY_IDVALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-4月-112202 b
SQL> select constraint_name,constraint_type,status,validated from User_Constraints where constraint_name in ('IND_T_NO_PAR','IND_T_PAR_RANGE');
CONSTRAINT_NAMEC STATUSVALIDATED
------------------------------ - -------- -------------
IND_T_PAR_RANGEP ENABLEDVALIDATED
IND_T_NO_PARP DISABLED NOT VALIDATED
SQL> select * from t_no_par;
TIMEIDCITY_IDVALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-3月-111101 a
SQL> select * from t_par_range;
TIMEIDCITY_IDVALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-4月-112202 b
SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes;
alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes
*
第1行出现错误:
ORA-14097: ALTER TABLE EXCHANGE PARTITION中的列类型或大小不匹配
--再想将刚交换到非分区表的数据交换回去,已经不可能了
SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes;
alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes
*
第1行出现错误:
ORA-14097: ALTER TABLE EXCHANGE PARTITION中的列类型或大小不匹配
SQL> select * from t_no_par;
TIMEIDCITY_IDVALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-3月-111101 a
SQL> select * from t_par_range;
TIMEIDCITY_IDVALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-4月-112202 b
SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par ;
alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par
*
第1行出现错误:
ORA-14097: ALTER TABLE EXCHANGE PARTITION中的列类型或大小不匹配
SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par without validation;
alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par without validation
*
第1行出现错误:
ORA-14097: ALTER TABLE EXCHANGE PARTITION中的列类型或大小不匹配
--注意到,无论使用何种添加选项手段,都已经不行了
SQL> alter table t_no_par enable constraint ind_t_no_par;
表已更改。
SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes;
表已更改。
--而一旦再次enable非分区表的primary key约束,又可以做数据交换了
SQL> select * from t_no_par;
未选定行
SQL> select * from t_par_range;
TIMEIDCITY_IDVALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-3月-111101 a
01-4月-112202 b