oracle分割槽及分割槽索引partition_partition index_維護(三)

 

------------交換分割槽,用普通表和分割槽表某個分割槽交換,即把普通表的資料可以轉化到分割槽表中

---如下說明要進行交換分割槽,普通表要與分割槽的鍵對應,建立主鍵約束,不然會報錯
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