ORACLE分区表日常维护方法是什么

这篇文章主要讲解了“ORACLE分区表日常维护方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“ORACLE分区表日常维护方法是什么”吧！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_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
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 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的分区
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_NAMEand 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_04 values 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 TEST_RANGE_SAL_MAX MAXVALUE 0
TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_04 4000 0

需要注意的是：对于默认条件的分区进行删除，其数据不会重分布到其他分区，而是删除数据。因此在生产环境使用需慎重。
至此，增加分区维护操作的介绍结束。

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

–查看当前分区对应的表空间情况
SQL> 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

–执行移动分区操作
SQL> alter table TEST_RANGE_PARTITION move partition TEST_RANGE_SAL_01 tablespace PARTITION_TS;
Table altered.

–验证移动后，分区所在的表空间
SQL> 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的组合分区表
SQL> CREATE TABLE “EMPLOYEE_LIST_LIST_PART”
( “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 LIST (DEPTNO)
SUBPARTITION BY LIST (JOB)
(
PARTITION EMPLOYEE_DEPTNO_10 VALUES (10)
( SUBPARTITION EMPLOYEE_10_JOB_MAGAGER VALUES (‘MANAGER’),
SUBPARTITION EMPLOYEE_10_JOB_DEFAULT VALUES (DEFAULT)
),
PARTITION EMPLOYEE_DEPTNO_20 VALUES (20)
( SUBPARTITION EMPLOYEE_20_JOB_MAGAGER VALUES (‘MANAGER’),
SUBPARTITION EMPLOYEE_20_JOB_DEFAULT VALUES (DEFAULT)
),
PARTITION EMPLOYEE_DEPTNO_OTHERS VALUES (DEFAULT)
( SUBPARTITION EMPLOYEE_30_JOB_MAGAGER VALUES (‘MANAGER’),
SUBPARTITION EMPLOYEE_30_JOB_DEFAULT VALUES (DEFAULT)
)
);

Table created.

–查看当前该组合分区所在表空间的信息
SQL> 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

–移动组合分区表的区分
SQL> 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

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

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

–移动子分区
SQL> alter table EMPLOYEE_LIST_LIST_PART move subpartition EMPLOYEE_20_JOB_MAGAGER tablespace PARTITION_TS;
Table altered.

SQL> alter table EMPLOYEE_LIST_LIST_PART move subpartition EMPLOYEE_20_JOB_DEFAULT tablespace PARTITION_TS;
Table altered.

–修改分区的默认属性
SQL> ALTER TABLE EMPLOYEE_LIST_LIST_PART MODIFY DEFAULT ATTRIBUTES FOR PARTITION EMPLOYEE_DEPTNO_20 tablespace PARTITION_TS;

Table altered.

–验证移动分区后的结果
SQL> 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操作，删除数据的效率会更高。而且会降低高水位线。

演示如下：

–查看当前测试表分区情况及分区中的记录数
SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions 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

–执行截断分区操作
SQL> alter table TEST_RANGE_PARTITION truncate partition TEST_RANGE_SAL_02;
Table truncated.

–重新收集最新的测试表的统计信息
SQL> analyze table TEST_RANGE_PARTITION compute statistics;

Table analyzed.

–验证截断操作后，分区的记录数变化
SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions 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）
对于分区的删除操作，需要注意，在删除分区后，分区所记录的数据，不会重分布至其他分区中，而是被一并删除。

–检查当前分区表的分区情况，以及数据的分布情况
SQL> 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 0
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 0
TEST_RANGE_PARTITION TEST_RANGE_SAL_04 USERS 0

–执行分区的删除操作
SQL> alter table TEST_RANGE_PARTITION drop partition TEST_RANGE_SAL_04;

Table altered.

–再次检查分区表的分区情况，以及数据的分布情况
SQL> 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 0
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 0

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

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

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

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

–清空测试分区表中的所有数据
SQL> truncate table TEST_RANGE_PARTITION;

Table truncated.

–重新加载测试分区表的数据
SQL> insert into TEST_RANGE_PARTITION select * from emp;

14 rows created.

SQL> commit;

Commit complete.

–重新收集测试表的统计信息
SQL> analyze table TEST_RANGE_PARTITION compute statistics;

Table analyzed.

–查看此时，数据在分区间的分布情况
SQL> 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的具体数据信息:
SQL> 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
将SAL>=4000的数据保留在分区TEST_RANGE_SAL_MAX中。