博文作者：Yumiko_sunny 博客链接：http://www.cnblogs.com/yumiko/

前言

本文着重总结 分区表的日常维护操作以及相应的注意事项 。
本文涉及的日常维护内容包括：

增加分区（add）
移动分区（move）
截断分区（truncate）
删除分区（drop）
拆分分区（split）
合并分区（merge） --hash分区不适用
交换分区（exchange）
收缩分区（coalesce） --仅适用于hash分区

本文涉及一些非分区表至分区表的迁移方法的思路，以及一些日常维护操作在特殊情况下的处理方法。
本文演示涉及的测试分区表，若无特殊创建或者说明，默认使用“测试表准备”部分提及的测试表。
本文演示使用的数据库版本为oracle 11.2.0.4。

需要注意：关于分区表日常维护操作，对于分区表索引的影响未提及，会在后面总结分区表索引时进行阐述说明。

1、测试表准备

为了便于具体的操作演示，首先准备一张RANGE型的测试分区表TEST_RANGE_PARTITION。

这里的测试数据来源于oracle测试用户scott下的emp表。


--创建分区表TEST_RANGE_PARTITION


--这里通过dbms_metadata.get_ddl获得emp表的建表结构进而修改




Yumiko_sunny@OA01> CREATE TABLE "SCOTT"."TEST_RANGE_PARTITION"  

2

     (    "EMPNO" NUMBER(

4

,


),  

3

          "ENAME" VARCHAR2(

10

),  

4

          "JOB" VARCHAR2(

9

),  

5

          "MGR" NUMBER(

4

,


),  

6

          "HIREDATE" DATE,  

7

          "SAL" NUMBER(

7

,

2

),  

8

          "COMM" NUMBER(

7

,

2

),  

9

          "DEPTNO" NUMBER(

2

,


) 

10



     ) 
 



11

    PARTITION BY RANGE ("SAL") 

12

     (PARTITION "TEST_RANGE_SAL_01" VALUES LESS THAN (

1000

) 

13

      PARTITION "TEST_RANGE_SAL_02" VALUES LESS THAN (

2000

) 
 

14

      PARTITION "TEST_RANGE_SAL_03" VALUES LESS THAN (

3000

) 
 

15

      PARTITION "TEST_RANGE_SAL_MAX" VALUES LESS THAN (MAXVALUE)   

16

     );
Table created.
Yumiko_sunny@OA01> insert into TEST_RANGE_PARTITION select * from emp;

14

 rows created.
Yumiko_sunny@OA01> commit;
Commit complete.

通过下面的方法，了解关于上面创建分区表的数据分布基本情况。



--查询分表各分区的条件以及数据库分布情况




--可以看到此时NUM_ROWS列为空，主要是因为表的的统计信息未收集导致。



Yumiko_sunny@OA01> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS 
  

2

  from user_part_tables a,user_tab_partitions b 
  

3

  where a.TABLE_NAME=b.TABLE_NAME; 
TABLE_NAME                     PARTITION PARTITION_NAME       HIGH_VALUE    NUM_ROWS
------------------------------ --------- -------------------- ----------- ----------
TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_01    

1000

TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_02    

2000

TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_03    

3000

TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_MAX   MAXVALUE

--收集分区表TEST_RANGE_PARTITION的统计信息



Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics;
Table analyzed.



--可以看到，此时各分区的数据情况已经显示出来

Yumiko_sunny@OA01> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS 
  

2

  from user_part_tables a,user_tab_partitions b 
  

3

  where a.TABLE_NAME=b.TABLE_NAME; 
TABLE_NAME                     PARTITION PARTITION_NAME       HIGH_VALUE    NUM_ROWS
------------------------------ --------- -------------------- ----------- ----------
TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_01    

1000

                 

2

TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_02    

2000

                 

6

TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_03    

3000

                 

3

TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_MAX   MAXVALUE             

3

通过上面的操作，已经成功创建了一张RANGE型的分区表。

下面将依托这张表，介绍分区表的日常维护操作。

2、增加分区维护操作（add）

增加分区维护操作，顾名思义，主要针对当前分区表进行添加新分区的操作。

当分区表存在默认条件分区，如：RANGE分区表的MAXVALUE分区、LIST分区表的DEFAULT分区，此时增加分区操作会报错。

下面尝试通过增加分区操作，直接为测试表增加分区TEST_RANGE_SAL_04

Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_04 values less than(4000);
alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_04 values less than(4000)
                                               *ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

可以看到，针对存在默认条件的分区表，无法执行增加分区操作。

解决办法：

1、删除原默认条件分区，待增加分区后，再重新添加默认条件分区。
2、 使用拆分分区（split）的方式，后面介绍 。

这里，我们尝试下解决办法1的方法进行操作。



--删除存在默认条件MAXVALUE的分区



Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION drop partition TEST_RANGE_SAL_MAX;
Table altered.



--重新收集分区表的统计信息

Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics;
Table analyzed.



--观察分区表的信息，可以看到此时默认条件MAXVALUE的分区已经不存在

Yumiko_sunny@OA01> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS 
  

2

  from user_part_tables a,user_tab_partitions b 
  

3

  where a.TABLE_NAME=b.TABLE_NAME; 
TABLE_NAME                     PARTITION PARTITION_NAME       HIGH_VALUE    NUM_ROWS
------------------------------ --------- -------------------- ----------- ----------
TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_01    

1000

                 

2

TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_02    

2000

                 

6

TEST_RANGE_PARTITION           RANGE     TEST_RANGE_SAL_03    

3000

                 

3




--增加新分区TEST_RANGE_SAL_04

Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_04 values less than(

4000

);
Table altered.



--重新增加默认条件MAXVALUE分区

Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_MAX values less than(maxvalue);
Table altered.

通过上面的方法，已经完成了增加分区的操作。下面进一步验证增加分区的操作。



--重新收集测试分区表的统计信息



Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics;
Table analyzed.



--查看分区表信息，可以看到上面增加的新分区

Yumiko_sunny@OA01> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS 
  

2

  from user_part_tables a,user_tab_partitions b 
  

3

  where a.TABLE_NAME=b.TABLE_NAME; 
TABLE_NAME            PARTITION PARTITION_NAME     HIGH_VALUE   NUM_ROWS
--------------------- --------- ------------------ ----------- ---------
TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_01  

1000

                

2

TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_02  

2000

                

6

TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_03  

3000

                

3



TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_MAX MAXVALUE            0
TEST_RANGE_PARTITION  RANGE     TEST_RANGE_SAL_04  4000                0

需要注意的是： 对于默认条件的分区进行删除，其数据不会重分布到其他分区，而是删除数据。因此在生产环境使用需慎重 。

至此，增加分区维护操作的介绍结束。

3、移动分区维护操作（move）

移动分区维护操作，主要是将分区从一个表空间迁移至另一个表空间中。



--查看当前分区对应的表空间情况



Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME       TABLESPACE_NAME
------------------------------ -------------------- ------------------------------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_02    USERS
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03    USERS


TEST_RANGE_PARTITION           TEST_RANGE_SAL_01    USERS


TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX   USERS
TEST_RANGE_PARTITION           TEST_RANGE_SAL_04    USERS

--执行移动分区操作

Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION move partition TEST_RANGE_SAL_01 tablespace PARTITION_TS;
Table altered.



--验证移动后，分区所在的表空间

Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME       TABLESPACE_NAME
------------------------------ -------------------- ------------------------------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_02    USERS
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03    USERS


TEST_RANGE_PARTITION           TEST_RANGE_SAL_01    PARTITION_TS


TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX   USERS
TEST_RANGE_PARTITION           TEST_RANGE_SAL_04    USERS

需要注意的是：

对于组合分区，无法直接移动分区 ，否则会抛出ORA-14257错误，示例如下：



--准备一张list-list的组合分区表



Yumiko_sunny@OA01> CREATE TABLE "EMPLOYEE_LIST_LIST_PART"  

2

      ( "EMPNO" NUMBER(

4

,


),  

3

        "ENAME" VARCHAR2(

10

),  

4

        "JOB" VARCHAR2(

9

),  

5

        "MGR" NUMBER(

4

,


),  

6

        "HIREDATE" DATE,  

7

        "SAL" NUMBER(

7

,

2

),  

8

        "COMM" NUMBER(

7

,

2

),  

9

        "DEPTNO" NUMBER(

2

,


) 

10

     ) 

11

     PARTITION BY LIST (DEPTNO) 

12

     SUBPARTITION BY LIST (JOB) 

13

     ( 

14

     PARTITION EMPLOYEE_DEPTNO_10 VALUES (

10

) 
 

15

       ( SUBPARTITION EMPLOYEE_10_JOB_MAGAGER VALUES ('MANAGER'), 

16

         SUBPARTITION EMPLOYEE_10_JOB_DEFAULT VALUES (DEFAULT) 

17

       ), 

18

     PARTITION EMPLOYEE_DEPTNO_20 VALUES (

20

) 
 

19

       ( SUBPARTITION EMPLOYEE_20_JOB_MAGAGER VALUES ('MANAGER'), 

20

         SUBPARTITION EMPLOYEE_20_JOB_DEFAULT VALUES (DEFAULT) 

21

       ), 

22

     PARTITION EMPLOYEE_DEPTNO_OTHERS VALUES (DEFAULT) 
 

23

       ( SUBPARTITION EMPLOYEE_30_JOB_MAGAGER VALUES ('MANAGER'), 

24

         SUBPARTITION EMPLOYEE_30_JOB_DEFAULT VALUES (DEFAULT) 

25

       ) 

26

     );
Table created.



--查看当前该组合分区所在表空间的信息

Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,TABLESPACE_NAME from user_tab_subpartitions;
TABLE_NAME              PARTITION_NAME         SUBPARTITION_NAME        TABLESPACE_NAME
----------------------- ---------------------- ------------------------ ---------------
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10     EMPLOYEE_10_JOB_MAGAGER  USERS
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10     EMPLOYEE_10_JOB_DEFAULT  USERS


EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20     EMPLOYEE_20_JOB_MAGAGER  USERS
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20     EMPLOYEE_20_JOB_DEFAULT  USERS


EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_MAGAGER  USERS
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_DEFAULT  USERS



--移动组合分区表的区分

Yumiko_sunny@OA01> alter table EMPLOYEE_LIST_LIST_PART  move partition EMPLOYEE_DEPTNO_20 tablespace PARTITION_TS;
alter table EMPLOYEE_LIST_LIST_PART  move partition EMPLOYEE_DEPTNO_20 tablespace PARTITION_TS
                                                    *


ERROR at line 1:
ORA-14257: cannot move partition other than a Range, List, System, or Hash partition

通过上面的演示，可以清楚的看到，对于组合分区，无法直接移动分区至新的表空间。

解决办法：

移动分区表的子分区，然后修改当前所在分区的属性即可。具体演示如下：



--移动子分区



Yumiko_sunny@OA01> alter table EMPLOYEE_LIST_LIST_PART  move subpartition EMPLOYEE_20_JOB_MAGAGER tablespace PARTITION_TS;
Table altered.
Yumiko_sunny@OA01> alter table EMPLOYEE_LIST_LIST_PART  move subpartition EMPLOYEE_20_JOB_DEFAULT tablespace PARTITION_TS;
Table altered.



--修改分区的默认属性

Yumiko_sunny@OA01> ALTER TABLE EMPLOYEE_LIST_LIST_PART MODIFY DEFAULT ATTRIBUTES FOR PARTITION EMPLOYEE_DEPTNO_20  

2

  tablespace PARTITION_TS;
Table altered.



--验证移动分区后的结果

Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,TABLESPACE_NAME from user_tab_subpartitions;
TABLE_NAME              PARTITION_NAME         SUBPARTITION_NAME        TABLESPACE_NAME
----------------------- ---------------------  -----------------------  ---------------
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10     EMPLOYEE_10_JOB_MAGAGER  USERS
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10     EMPLOYEE_10_JOB_DEFAULT  USERS


EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20     EMPLOYEE_20_JOB_MAGAGER  PARTITION_TS
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20     EMPLOYEE_20_JOB_DEFAULT  PARTITION_TS


EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_MAGAGER  USERS
EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_DEFAULT  USERS

可以看到，通过移动子分区的方法，完成了对于组合分区的移动操作。

4、截断分区维护操作（truncate）

截断分区维护操作，相对于传统的delete操作，删除数据的效率会更高。而且会降低高水位线。

演示如下：



--查看当前测试表分区情况及分区中的记录数



Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions  

2

  where PARTITION_NAME='TEST_RANGE_SAL_02' or PARTITION_NAME='TEST_RANGE_SAL_03';
TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------

TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    

6


TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    

3




--执行截断分区操作

Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION truncate partition TEST_RANGE_SAL_02;
Table truncated.



--重新收集最新的测试表的统计信息

Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics;
Table analyzed.



--验证截断操作后，分区的记录数变化

Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions  

2

  where PARTITION_NAME='TEST_RANGE_SAL_02' or PARTITION_NAME='TEST_RANGE_SAL_03';
TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------

TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                   

 0


TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    

3

从上面的演示中可以看到，通过truncate操作，测试表的TEST_RANGE_SAL_02分区数据被清空。

至此，演示完毕。

5、删除分区维护操作（drop）

对于分区的删除操作，需要注意，在删除分区后，分区所记录的数据，不会重分布至其他分区中，而是被一并删除。



--检查当前分区表的分区情况，以及数据的分布情况



Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    


TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    

3

TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             

2

TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    



TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    






--执行分区的删除操作

Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION drop partition TEST_RANGE_SAL_04;
Table altered.



--再次检查分区表的分区情况，以及数据的分布情况

Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    


TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    

3

TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             

2

TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS

可以看到，分区的删除操作不会影响数据的分布情况。

6、拆分分区维护操作（split）

在“增加分区维护操作”部分，提到了对于存在默认条件的分区表增加分区的的两种办法，这里将介绍通过拆分分区的办法来增加分区。

需要注意： 在目标分区拆分后，被拆分的分区会按照拆分规则，将数据进行重分布 。

演示实例：

首先，将测试表的数据分布还原至初建时的数据分布态。



--清空测试分区表中的所有数据



Yumiko_sunny@OA01> truncate table TEST_RANGE_PARTITION;
Table truncated.



--重新加载测试分区表的数据

Yumiko_sunny@OA01> insert into TEST_RANGE_PARTITION select * from emp;

14

 rows created.
Yumiko_sunny@OA01> commit;
Commit complete.



--重新收集测试表的统计信息

Yumiko_sunny@OA01> analyze table TEST_RANGE_PARTITION compute statistics;
Table analyzed.



--查看此时，数据在分区间的分布情况

Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    

6

TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    

3

TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             

2

TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    

3

查看此时，存在默认条件MAXVALUE的分区TEST_RANGE_SAL_MAX的具体数据信息。

Yumiko_sunny@OA01> select * from TEST_RANGE_PARTITION partition(TEST_RANGE_SAL_MAX);
     EMPNO ENAME      JOB              MGR HIREDATE          SAL     COMM    DEPTNO
---------- ---------- --------- ---------- ------------ -------- -------- ---------      7788 SCOTT      ANALYST         7566 19-APR-87        3000                 20
      7839 KING       PRESIDENT            17-NOV-81        5000                 10
      7902 FORD       ANALYST         7566 03-DEC-81        3000                 20

下面针对上面的分区TEST_RANGE_SAL_MAX进行拆分处理，其中：

将SAL>=3000且SAL<4000的数据放入新的分区TEST_RANGE_SAL_04。
将SAL>=4000的数据保留在分区TEST_RANGE_SAL_MAX中。



--针对目标分区，执行拆分分区维护操作


--依据上面的需求，将数据拆分至分区TEST_RANGE_SAL_04以及TEST_RANGE_SAL_MAX中



Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION split partition TEST_RANGE_SAL_MAX at (

4000

) into  

2

  (partition TEST_RANGE_SAL_04,partition TEST_RANGE_SAL_MAX);
Table altered.



--查看此时测试分区表的分区情况，以及数据分布情况

Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    

6

TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    

3

TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             

2

TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    

2

TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    

1

验证分区中实际的数据内容

Yumiko_sunny@OA01> select * from TEST_RANGE_PARTITION partition(TEST_RANGE_SAL_04);
     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20Yumiko_sunny@OA01> select * from TEST_RANGE_PARTITION partition(TEST_RANGE_SAL_MAX);
     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------      7839 KING       PRESIDENT            17-NOV-81          5000                    10

可以看到，经过拆分，数据已按之前的需求，分别存储在两个分区中。

7、合并分区维护操作（merge）

合并分区操作，主要是将不同的分区，通过分区的合并，进行整合。

需要注意：

对于list分区，合并的分区无限制要求。
对于range分区，合并的分区必须相邻，否则无法进行合并操作。
对于hash分区，无法进行合并分区操作 。

此外，对于range分区，下限值由边界值较低的分区决定，上限值由边界值较高的分区决定。

演示示例：

通过合并分区技术，将测试表的分区TEST_RANGE_SAL_01以及分区TEST_RANGE_SAL_02进行合并，具体如下：



--查看当前分区表的分区情况



Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------

TEST_RANGE_PARTITION           TEST_RANGE_SAL_02         USERS                    

6


TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    

3


TEST_RANGE_PARTITION           TEST_RANGE_SAL_01         PARTITION_TS             

2


TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    

2

TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    

1




--进行合并分区操作

Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION merge partitions 
  

2

  TEST_RANGE_SAL_01,TEST_RANGE_SAL_02  

3

  into partition TEST_RANGE_SAL_00;
Table altered.

--验证合并分区后的结果

Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    

3

TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    

2

TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    

1


TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                    

8

8、交换分区维护操作（exchange）

交换分区技术，主要是将一个非分区表的数据同“一个分区表的一个分区”进行数据交换。 支持双向交换，既可以从分区表的分区中迁移到非分区表，也可以从非分区表迁移至分区表的分区中 。
原则上， 非分区表的结构、数据分布等，要符合分区表的目标分区的定义规则 。

演示如下：

首先，清空测试分区表的数据

Yumiko_sunny@OA01> truncate table TEST_RANGE_PARTITION;
Table truncated.
Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    0TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                    0

创建一张基于emp表，sal<2000的测试非分区表emp_test。

Yumiko_sunny@OA01> create table emp_test as select * from emp where sal < 2000;
Table created.
Yumiko_sunny@OA01> select count(*) from emp_test;
  COUNT(*)
----------         8

注意，此时非分区表的数据量为8条记录。

执行交换分区操作，观察分区表的记录变化，以及非分区表的记录变化



--执行分区交换操作



Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test;
Table altered.
Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    



TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                    

8


TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    


TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    


Yumiko_sunny@OA01> select count(*) from emp_test; 

 COUNT(*)
----------

可以看到，通过分去交换，非分区表的数据转移至分区表中，同时非分区表的记录被清除。

再次执行交换分区操作，观察分区表的记录变化，以及非分区表的记录变化

Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test;
Table altered.
Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    0TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                    0Yumiko_sunny@OA01> select count(*) from emp_test;  COUNT(*)
----------         8

可以看到，此时分区表的数据又再次转移回至非分区表，证明了前面所述，分区交换技术，既可以从分区表的分区中迁移到非分区表，也可以从非分区表迁移至分区表的分区中。

若非分区表的数据，不符合分区表的分区规则，此时交换会抛出ORA-14099错误。



--清空上面测试非分区表的数据



Yumiko_sunny@OA01> truncate table emp_test;
Table truncated.



--加载emp的所有数据至该测试非分区表




--之所以使用测试非分区表，是考虑emp表以后做其他实验时可能还需要其中的数据




--通过这样操作，测试非分区表的数据，既存在sal<2000的数据，也存在sal>2000的数据

Yumiko_sunny@OA01> insert into emp_test select * from emp;

14

 rows created.
Yumiko_sunny@OA01> commit;
Commit complete.





--尝试交换分区，观察结果

Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test;
alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test
                                                                                 *

ERROR at line 

1

:
ORA-

14099

: all rows in table do not qualify for specified partition

可以看到，由于TEST_RANGE_SAL_00分区的限制条件为sal<2000，而测试非分区表的数据包含了sal>2000的数据，因此交换失败。

解决办法：

通过without validation子句，可以避免数据校验，而交换成功。 但会存在与分区规则相悖的数据 ，因此该方法要慎重。

Yumiko_sunny@OA01> alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test without validation;
Table altered.
Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
TEST_RANGE_PARTITION           TEST_RANGE_SAL_03         USERS                    0TEST_RANGE_PARTITION           TEST_RANGE_SAL_00         USERS                   14TEST_RANGE_PARTITION           TEST_RANGE_SAL_04         USERS                    0TEST_RANGE_PARTITION           TEST_RANGE_SAL_MAX        USERS                    0

技术方案扩展思路：

若打算采用交换分区的方法，以实现非分区表到分区表的转换，可以采用先创建一个只有默认条件的单一分区的分区表，在分区交换数据后，根据实际需要，通过前面提到的“拆分分区”的方法进行分区操作。

9、收缩分区维护操作（coalesce）

收缩分区维护操作， 仅仅可以在hash分区以及组合分区的hash子分区上进行使用 。

通过使用收缩分区技术，可以收缩当前hash分区的分区数量。

对于hash分区的数据，在收缩过程中，oracle会自动完成数据在分区间的重分布。

演示如下：

首先基于emp表的数据，创建一张hash分区表

Yumiko_sunny@OA01> CREATE TABLE "EMPLOYEE_HASH_PART"  2      ( "EMPNO" NUMBER(4,0),  3        "ENAME" VARCHAR2(10),  4        "JOB" VARCHAR2(9),  5        "MGR" NUMBER(4,0),  6        "HIREDATE" DATE,  7        "SAL" NUMBER(7,2),  8        "COMM" NUMBER(7,2),  9        "DEPTNO" NUMBER(2,0) 10      ) 11      PARTITION BY HASH (ENAME) 12      ( 13      PARTITION EMPLOYEE_PART01, 14      PARTITION EMPLOYEE_PART02 15     );  
Table created.
Yumiko_sunny@OA01> insert into EMPLOYEE_HASH_PART select * from emp;14 rows created.
Yumiko_sunny@OA01> commit;
Commit complete.
Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------EMPLOYEE_HASH_PART             EMPLOYEE_PART02           USERS                    6EMPLOYEE_HASH_PART             EMPLOYEE_PART01           USERS                    8

执行收缩分区操作

Yumiko_sunny@OA01> alter table EMPLOYEE_HASH_PART coalesce partition;
Table altered.
Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------EMPLOYEE_HASH_PART             EMPLOYEE_PART01           USERS                   14

可以看到，通过收缩分区，原本两个分区整合到一个，而且数据也同时被整合。

需要注意：

当hash分区中只有一个分区时，此时无法进行收缩操作。

Yumiko_sunny@OA01> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME            TABLESPACE_NAME   NUM_ROWS
------------------------------ ------------------------- --------------- ----------
EMPLOYEE_HASH_PART             EMPLOYEE_PART01           USERS                   14Yumiko_sunny@OA01> alter table EMPLOYEE_HASH_PART coalesce partition;
alter table EMPLOYEE_HASH_PART coalesce partition
            *ERROR at line 1:
ORA-14285: cannot COALESCE the only partition of this hash partitioned table or index

ORACLE分区表梳理系列（二）- 分区表日常维护及注意事项（红字需要留意）

博文作者：Yumiko_sunny 博客链接：http://www.cnblogs.com/yumiko/

前言

1、测试表准备

2、增加分区维护操作（add）

3、移动分区维护操作（move）

4、截断分区维护操作（truncate）

5、删除分区维护操作（drop）

6、拆分分区维护操作（split）

7、合并分区维护操作（merge）

8、交换分区维护操作（exchange）

9、收缩分区维护操作（coalesce）