12C新特性：多个分区合并为一个分区；一个分区拆分为多个分区

1、多个分区合并为一个分区

SQL> ALTER TABLE t_whs_p MERGE
  2  PARTITIONS p2, p3, p4
  3  INTO PARTITION p2_4;

Table altered.
SQL> COL TABLE_NAME FORMAT A15
SQL> COL PARTITION_NAME FORMAT A15
SQL> select TABLE_NAME,PARTITION_NAME FROM dba_tab_partitions where TABLE_OWNER='TEST' AND TABLE_NAME='T_WHS_P';

TABLE_NAME      PARTITION_NAME
--------------- ---------------
T_WHS_P         P1
T_WHS_P         P2_4
T_WHS_P         P5

SQL> 

2、一个分区拆分为多个分区

SQL> ALTER TABLE t_whs_p SPLIT
  2  PARTITION p2_4 INTO (
  3  PARTITION p2 VALUES LESS THAN (20),
  4  PARTITION p3 VALUES LESS THAN (30),
  5  PARTITION p4 VALUES LESS THAN (40)
  6  );
)
*
ERROR at line 6:
ORA-14805: last resulting partition cannot contain bounds

最后一个分区不能包含LESS THAN，因为需要继承原先分区的LESS THAN

SQL> ALTER TABLE t_whs_p SPLIT
  2  PARTITION p2_4 INTO (
  3  PARTITION p2 VALUES LESS THAN (20),
  4  PARTITION p3 VALUES LESS THAN (30),
  5  PARTITION p4 
  6  );

Table altered.

SQL> select TABLE_NAME,PARTITION_NAME FROM dba_tab_partitions where TABLE_OWNER='TEST' AND TABLE_NAME='T_WHS_P';

TABLE_NAME      PARTITION_NAME
--------------- ---------------
T_WHS_P         P1
T_WHS_P         P2
T_WHS_P         P3
T_WHS_P         P4
T_WHS_P         P5