Oracle 12c 新特性 --- 拆分表分区

概念 

The partition maintenance operations SPLIT PARTITION and SPLIT SUBPARTITION can now be executed as online operations for heap organized tables, allowing the concurrent DML operations with the ongoing partition maintenance operation.
分区维护操作分离分区和分离子分区现在可以作为堆有组织的表的联机操作执行，从而允许并发的DML操作与正在进行的分区维护操作。

Allowing any partition maintenance operation to become truly online enables the customers to schedule and execute all of these operations as needed, without having to plan around periods of query-only windows. This functionality both increases application availability and simplifies application development.

允许任何分区维护操作成为真正的在线，使客户能够按照需要计划并执行所有这些操作，而不必计划只需要查询的窗口期。这个功能既提高了应用程序的可用性，又简化了应用程序开发。

You can split the contents of a partition into two new partitions.
The SPLIT PARTITION clause of the ALTER TABLE or ALTER INDEX statement is used to redistribute the contents of a partition into two new partitions. Consider doing this when a partition becomes too large and causes backup, recovery, or maintenance operations to take a long time to complete or it is felt that there is simply too much data in the partition. You can also use the SPLIT PARTITION clause to redistribute the I/O load. This clause cannot be used for hash partitions or subpartitions.

您可以将分区的内容划分为两个新的分区。
ALTER TABLE或ALTER INDEX语句的分割分区子句用于将一个分区的内容重新分配到两个新的分区中。考虑这样做，当一个分区变得太大，导致备份、恢复或维护操作需要很长时间才能完成，或者感觉分区中有太多的数据。您还可以使用SPLIT PARTITION子句来重新分配I / O负载。这个子句不能用于哈希分区或分区。

 

实验

1) 会话1 创建分区表
[leo@www.cndba.cn ~]$ sqlplus test/test@pdbcndba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 26 23:11:04 2017

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

Last Successful login time: Sat Aug 26 2017 23:10:35 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> CREATE TABLE orders
 (prod_id       NUMBER(6),
  cust_id       NUMBER,
  time_id       DATE,
  channel_id    CHAR(1),
  promo_id      NUMBER(6),
  quantity_sold NUMBER(3),
  amount_sold   NUMBER(10,2)
  ) 
  PARTITION BY RANGE (time_id)
  (PARTITION sales_q1_2016 VALUES LESS THAN (TO_DATE('01-APR-2016','dd-MON-yyyy')),
   PARTITION sales_q2_2016 VALUES LESS THAN (TO_DATE('01-JUL-2016','dd-MON-yyyy')),
   PARTITION sales_q3_2016 VALUES LESS THAN (TO_DATE('01-OCT-2016','dd-MON-yyyy')),
   PARTITION sales_q4_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','dd-MON-yyyy')) 
   );
   
Table created.

2） 查看分区
SQL> SELECT partition_name
FROM   user_tab_partitions
WHERE  table_name = 'ORDERS';  2    3  

PARTITION_NAME
--------------------------------------------------------------------------------
SALES_Q1_2016
SALES_Q2_2016
SALES_Q3_2016
SALES_Q4_2016

4 rows selected.

3） 另开一个会话插入数据，未提交
[leo@www.cndba.cn ~]$ sqlplus test/test@pdbcndba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 26 23:12:24 2017

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

Last Successful login time: Sat Aug 26 2017 23:11:04 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> INSERT INTO orders (prod_id,time_id ) VALUES (4,TO_DATE('20-DEC-2016','dd-MON-yyyy'));

1 row created.


4）在会话1 拆分sales_q4_2016分区，因为会话2 dml操作未提交，拆分分区等待会话2提交
SQL> ALTER TABLE orders 
  SPLIT PARTITION sales_q4_2016 INTO
  (PARTITION sales_oct_2016 VALUES LESS THAN (TO_DATE('01-NOV-2016','dd-MON-yyyy')),
   PARTITION sales_nov_2016 VALUES LESS THAN (TO_DATE('01-DEC-2016','dd-MON-yyyy')),
   PARTITION sales_dec_2016
   )
  ONLINE;  

5）会话2 提交，会话1拆分分区执行成功
SQL> commit;

Commit complete.

SQL> ALTER TABLE orders 
  SPLIT PARTITION sales_q4_2016 INTO
  (PARTITION sales_oct_2016 VALUES LESS THAN (TO_DATE('01-NOV-2016','dd-MON-yyyy')),
   PARTITION sales_nov_2016 VALUES LESS THAN (TO_DATE('01-DEC-2016','dd-MON-yyyy')),
   PARTITION sales_dec_2016
   )
  ONLINE;  

Table altered.

6） 查看拆分之后的分区信息
SQL> SELECT partition_name
FROM   user_tab_partitions
WHERE  table_name = 'ORDERS';  2    3  

PARTITION_NAME
--------------------------------------------------------------------------------
SALES_DEC_2016
SALES_NOV_2016
SALES_OCT_2016
SALES_Q1_2016
SALES_Q2_2016
SALES_Q3_2016

6 rows selected.

 

更多关于拆分表分区的详细信息科参考官方文档

http://docs.oracle.com/database/122/VLDBG/maintenance-partition-tables-indexes.htm#VLDBG-GUID-6BB84952-7021-4CBA-91ED-180E0656E02B