oracle分区及分区索引partition_partition index_维护(三)

http://space.itpub.net/?uid-9240380-action-viewspace-itemid-751842

 

------------交换分区,用普通表和分区表某个分区交换,即把普通表的数据可以转化到分区表中

---如下说明要进行交换分区,普通表要与分区的键对应,建立主键约束,不然会报错
SQL> alter table t_test_partition_1 exchange partition p1_old with table t_repla
ce;
alter table t_test_partition_1 exchange partition p1_old with table t_replace
*
第 1 行出现错误:
ORA-14097: ALTER TABLE EXCHANGE PARTITION 中的列类型或大小不匹配

SQL> desc t_replace;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER(38)
 REQTIME                                            TIMESTAMP(6)
 CALL_IP                                            VARCHAR2(200)

SQL> desc t_test_partition_1;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 ID                                        NOT NULL NUMBER(38)
 REQTIME                                            TIMESTAMP(6)
 CALL_IP                                            VARCHAR2(200)

SQL> alter table t_replace add constraint pk_t_replace_id primary key(id);

表已更改。

SQL> alter table t_test_partition_1 exchange partition p1_old with table t_repla
ce;

表已更改。

SQL>

---分区交换导致被交换分区
SQL> select index_name,partition_name,status from user_ind_partitions where inde
x_name='IDX_T_TEST_PARTITION_1_ID';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_T_TEST_PARTITION_1_ID      P1_OLD                         UNUSABLE
IDX_T_TEST_PARTITION_1_ID      P2_IDX2                        USABLE
IDX_T_TEST_PARTITION_1_ID      P3_IDX3                        USABLE
IDX_T_TEST_PARTITION_1_ID      P4_IDX4                        USABLE
IDX_T_TEST_PARTITION_1_ID      P5_IDX5                        USABLE

-------重建失效的本地分区索引
SQL> alter index idx_t_test_partition_1_id rebuild partition p1_old;

索引已更改。

SQL> select index_name,partition_name,status from user_ind_partitions where inde
x_name='IDX_T_TEST_PARTITION_1_ID';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_T_TEST_PARTITION_1_ID      P1_OLD                         USABLE
IDX_T_TEST_PARTITION_1_ID      P2_IDX2                        USABLE
IDX_T_TEST_PARTITION_1_ID      P3_IDX3                        USABLE
IDX_T_TEST_PARTITION_1_ID      P4_IDX4                        USABLE
IDX_T_TEST_PARTITION_1_ID      P5_IDX5                        USABLE

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-751843/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-751843/