oracle10g--11gR2分区表汇总四
解释：
首先：分区表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_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IND_T_PAR_RANGE P_T_PAR_RANGE_0 USABLE
IND_T_PAR_RANGE P_T_PAR_RANGE_1 USABLE
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE index_NAME in ('IND_T_NO_PAR');
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IND_T_NO_PAR NORMAL VALID
--索引仍然有效
SQL> select constraint_name,constraint_type,status,validated from User_Constraints where constraint_name in ('IND_T_NO_PAR','IND_T_PAR_RANGE');
CONSTRAINT_NAME C STATUS VALIDATED
------------------------------ - -------- -------------
IND_T_PAR_RANGE P ENABLED VALIDATED
IND_T_NO_PAR P DISABLED NOT VALIDATED
--约束已经disable了，且not validated
SQL> select * from t_no_par;
未选定行
SQL> select * from t_par_range;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-3月 -11 1 10 1 a
01-4月 -11 2 20 2 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_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IND_T_NO_PAR NORMAL VALID
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IND_T_PAR_RANGE P_T_PAR_RANGE_0 USABLE
IND_T_PAR_RANGE P_T_PAR_RANGE_1 USABLE
--交换之后，各自约束和索引的状态
SQL> select * from t_no_par;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-3月 -11 1 10 1 a
SQL> select * from t_par_range;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-4月 -11 2 20 2 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_NAME C STATUS VALIDATED
------------------------------ - -------- -------------
IND_T_PAR_RANGE P ENABLED VALIDATED
IND_T_NO_PAR P DISABLED NOT VALIDATED
SQL> select * from t_no_par;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-3月 -11 1 10 1 a
SQL> select * from t_par_range;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-4月 -11 2 20 2 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;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-3月 -11 1 10 1 a
SQL> select * from t_par_range;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-4月 -11 2 20 2 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;
TIME ID CITY_ID VALUE1 VALUE2
-------------- ---------- ---------- ---------- ----------
01-3月 -11 1 10 1 a
01-4月 -11 2 20 2 b
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10037372/viewspace-689823/，如需转载，请注明出处，否则将追究法律责任。
