oracle分区注意点,ORACLE分区表梳理系列（二）- 分区表日常维护及注意事项

http://www.cnblogs.com/yumiko/p/6163523.html

前言:

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

本文涉及的日常维护内容包括：

增加分区(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表的建表结构进而修改

SQL> CREATE TABLE "SCOTT"."TEST_RANGE_PARTITION"

(    "EMPNO" NUMBER(4,0),

"ENAME" VARCHAR2(10),

"JOB" VARCHAR2(9),

"MGR" NUMBER(4,0),

"HIREDATE" DATE,

"SAL" NUMBER(7,2),

"COMM" NUMBER(7,2),

"DEPTNO" NUMBER(2,0)

)

PARTITION BY RANGE ("SAL")

(PARTITION "TEST_RANGE_SAL_01" VALUES LESS THAN (1000),

PARTITION "TEST_RANGE_SAL_02" VALUES LESS THAN (2000),

PARTITION "TEST_RANGE_SAL_03" VALUES LESS THAN (3000),

PARTITION "TEST_RANGE_SAL_MAX" VALUES LESS THAN (MAXVALUE)

);

Table created.

SQL> insert into TEST_RANGE_PARTITION select * from emp;

14 rows created.

SQL> commit;

Commit complete.

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

复制代码

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

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

SQL> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWSfrom user_part_tables a,user_tab_partitions bwhere a.TABLE_NAME=b.TABLE_NAME and a.table_name='TEST_RANGE_PARTITION';

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的统计信息

SQL> analyze table TEST_RANGE_PARTITION compute statistics;

Table analyzed.

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

SQL> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS from user_part_tables a,user_tab_partitions b where a.TABLE_NAME=b.TABLE_NAME and a.table_name='TEST_RANGE_PARTITION';

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

SQL> alter table TEST_RANGE_PARTITION addpartition TEST_RANGE_SAL_04values 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的分区

SQL> alter table TEST_RANGE_PARTITION drop partition TEST_RANGE_SAL_MAX;

Table altered.

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

SQL> analyze table TEST_RANGE_PARTITION compute statistics;

Table analyzed.

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

SQL> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS from user_part_tables a,user_tab_partitions b where a.TABLE_NAME=b.TABLE_NAME and a.table_name='TEST_RANGE_PARTITION';

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

SQL> alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_04values less than(4000);

Table altered.

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

SQL> alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_MAX values less than(maxvalue);

Table altered.

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

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

SQL> analyze table TEST_RANGE_PARTITION compute statistics;

Table analyzed.

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

SQL> select a.TABLE_NAME,PARTITIONING_TYPE,PARTITION_NAME,HIGH_VALUE,NUM_ROWS from user_part_tables a,user_tab_partitions b where a.TABLE_NAME=b.TABLE_NAME and a.table_name='TEST_RANGE_PARTITION';

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