oracle10g--11gR2分区表汇总三
3.2.6 有normal索引(分区表有全局索引，非分区表有索引)
紧接上个试验过程：
SQL>
SQL> create index ind_t_par_range on t_par_range(id);
索引已创建。
SQL> create index ind_t_no_par on t_no_par(id);
索引已创建。
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';
未选定行
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE index_NAME in ('IND_T_NO_PAR','IND_T_PAR_RANGE');
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IND_T_NO_PAR NORMAL VALID
IND_T_PAR_RANGE NORMAL VALID
SQL>
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;
alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes
*
第 1 行出现错误:
ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配
--此时再指定including indexes就报错了
SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par ;
表已更改。
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE index_NAME in ('IND_T_NO_PAR','IND_T_PAR_RANGE');
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IND_T_NO_PAR NORMAL UNUSABLE
IND_T_PAR_RANGE NORMAL UNUSABLE
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-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配
SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par ;
表已更改。
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> drop index ind_t_no_par;
索引已删除。
SQL> drop index ind_t_par_range;
索引已删除。
3.2.7 有约束(分区表有Primary key约束，非分区表有Primary key约束)
接上个试验过程：
3.2.7.1 (分区表、非分区表enable)—---exchange—----exchange解释：
首先：分区表和非分区表primary key 都是 enable的；
其次：交换分区且包括 including indexes;
然后：再次交换分区包括 including indexes;
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE index_NAME in ('IND_T_NO_PAR','IND_T_PAR_RANGE');
未选定行
SQL> alter table t_par_range add constraint ind_t_par_range using index local;
alter table t_par_range add constraint ind_t_par_range using index local
*
第 1 行出现错误:
ORA-00902: 无效数据类型
SQL> alter table t_par_range add constraint ind_t_par_range primary key(id) using index local;
alter table t_par_range add constraint ind_t_par_range primary key(id) using index local
*
第 1 行出现错误:
ORA-14039: 分区列必须构成 UNIQUE 索引的关键字列子集
SQL> alter table t_par_range add constraint ind_t_par_range primary key(time,id) using index local;
表已更改。
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE index_NAME in ('IND_T_NO_PAR','IND_T_PAR_RANGE');
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IND_T_PAR_RANGE NORMAL N/A
SQL> alter table t_no_par add constraint ind_t_no_par primary key(id) using index;
表已更改。
SQL> SELECT index_name,index_type,status FROM USER_INDEXES WHERE index_NAME in ('IND_T_NO_PAR','IND_T_PAR_RANGE');
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IND_T_NO_PAR NORMAL VALID
IND_T_PAR_RANGE NORMAL N/A
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 INDEX_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 ENABLED VALIDATED
SQL>
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;
alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par including indexes
*
第 1 行出现错误:
ORA-14130: UNIQUE 约束条件在 ALTER TABLE EXCHANGE PARTITION 中不匹配
--因为上边创建primary key时，分区表和非分区表创建的不一样
SQL> drop constraint ind_t_no_par;
drop constraint ind_t_no_par
*
第 1 行出现错误:
ORA-00950: 无效 DROP 选项
SQL> alter table t_no_par drop constraint ind_t_no_par;
表已更改。
SQL> select constraint_name,constraint_type,status,validated from User_Constraints where INDEX_name in ('IND_T_NO_PAR','IND_T_PAR_RANGE');
CONSTRAINT_NAME C STATUS VALIDATED
------------------------------ - -------- -------------
IND_T_PAR_RANGE P ENABLED VALIDATED
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');
未选定行
SQL> alter table t_no_par add constraint ind_t_no_par primary key(time,id) using 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 INDEX_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 ENABLED 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 * 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 INDEX_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 ENABLED VALIDATED
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 * 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;
表已更改。
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> 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 INDEX_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 ENABLED VALIDATED
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10037372/viewspace-689822/ ，如需转载，请注明出处，否则将追究法律责任。
