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博客

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

最新文章