Oracle 12c 新特性 --- 对多个分区进行维护操作

概述

Partition maintenance operations can be performed on multiple partitions as part of a single partition maintenance operation.
分区维护操作可以在多个分区上执行，作为单个分区维护操作的一部分。
A single partition maintenance operation working on multiple partitions at the same time simplifies application development and leads to more efficient partition maintenance using less system resources.
同时在多个分区上运行的单个分区维护操作简化了应用程序开发，并使用更少的系统资源导致更有效的分区维护。
更多分区表维护信息可参考：http://docs.oracle.com/database/121/VLDBG/GUID-A226B597-BCF1-49E2-8284-739A99D3F9ED.htm#VLDBG1118

实验

下面的例子展示了分区级别的功能。
1） 创建分区表
SQL> col table_name for a10
[leo@www.cndba.cn ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 18 10:49:54 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> DROP TABLE t2 PURGE;

Table dropped.

SQL> DROP TABLE t1 PURGE;

Table dropped.

SQL> CREATE TABLE t1
(id            NUMBER,
 description   VARCHAR2(50),
 created_date  DATE)
PARTITION BY RANGE (created_date)
(PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY'))); 

Table created.

SQL> col table_name for a10
SQL> col partition_name for a40
SQL> SELECT table_name,
       partition_name
FROM   user_tab_partitions where table_name in ('T1','T2')
ORDER BY 1,2;  2    3    4  

TABLE_NAME PARTITION_NAME
---------- ----------------------------------------
T1    PART_2014
2）增加多个分区
SQL> ALTER TABLE t1
ADD
 PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),
 PARTITION part_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')),
 PARTITION part_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')),
 PARTITION part_2018 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY'));

Table altered.

SQL> SELECT table_name,
       partition_name
FROM   user_tab_partitions where table_name in ('T1','T2')
ORDER BY 1,2;   2    3    4  

TABLE_NAME PARTITION_NAME
---------- ----------------------------------------
T1         PART_2014
T1         PART_2015
T1         PART_2016
T1         PART_2017
T1         PART_2018
3）合并多个子分区到一个分区
SQL> ALTER TABLE t1
MERGE
  PARTITIONS part_2015, part_2016, part_2017, part_2018
  INTO PARTITION part_2018;

Table altered.

SQL> SELECT table_name,
       partition_name
FROM   user_tab_partitions where table_name in ('T1','T2')
ORDER BY 1,2;  

TABLE_NAME PARTITION_NAME
---------- ----------------------------------------
T1        PART_2014
T1        PART_2018
4）拆分成多个分区
SQL> ALTER TABLE t1
SPLIT
  PARTITION part_2018 INTO (
    PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),
    PARTITION part_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')),
    PARTITION part_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')),
    PARTITION part_2018
); 

Table altered.

SQL> SELECT table_name,
       partition_name
FROM   user_tab_partitions where table_name in ('T1','T2')
ORDER BY 1,2;  2    3    4  

TABLE_NAME PARTITION_NAME
---------- ----------------------------------------
T1        PART_2014
T1        PART_2015
T1        PART_2016
T1        PART_2017
T1        PART_2018
5）TRUNCATE多个分区
SQL> ALTER TABLE t1
TRUNCATE
  PARTITION part_2014, part_2015, part_2016, part_2017, part_2018;  2    3  

Table truncated.

SQL> SELECT table_name,
       partition_name
FROM   user_tab_partitions where table_name in ('T1','T2')
ORDER BY 1,2;  2    3    4  

TABLE_NAME PARTITION_NAME
---------- ----------------------------------------
T1        PART_2014
T1        PART_2015
T1        PART_2016
T1        PART_2017
T1        PART_2018
6）删除多个分区
SQL> ALTER TABLE t1
DROP
  PARTITION part_2016, part_2017, part_2018;  2    3  

Table altered.

SQL> SELECT table_name,
       partition_name
FROM   user_tab_partitions where table_name in ('T1','T2')
ORDER BY 1,2;  2    3    4  

TABLE_NAME PARTITION_NAME
---------- ----------------------------------------
T1        PART_2014
T1        PART_2015

参考文档

http://docs.oracle.com/database/121/NEWFT/chapter12101.htm#NEWFT205

http://docs.oracle.com/database/121/VLDBG/GUID-A226B597-BCF1-49E2-8284-739A99D3F9ED.htm#VLDBG1118