1.增加一个分区

    1.1没有maxvalue的情况

CREATE TABLE T_PART(ID NUMBER PRIMARY KEY,
NAME VARCHAR2(20),
SALARY NUMBER,
S_TIME DATE)
PARTITION BY RANGE(S_TIME)
(
PARTITION P_1 VALUES LESS THAN (TO_DATE('2015-01-01','YYYY-MM-DD')), 
PARTITION P_2 VALUES LESS THAN (TO_DATE('2016-01-01','YYYY-MM-DD')), 
PARTITION P_3 VALUES LESS THAN (TO_DATE('2017-01-01','YYYY-MM-DD')), 
PARTITION P_4 VALUES LESS THAN (TO_DATE('2018-01-01','YYYY-MM-DD')), 
PARTITION P_5 VALUES LESS THAN (TO_DATE('2019-01-01','YYYY-MM-DD')), 
PARTITION P_6 VALUES LESS THAN (TO_DATE('2020-01-01','YYYY-MM-DD')));

增加一个分区
ALTER TABLE T_PART ADD PARTITION P_7 VALUESLESS THAN (TO_DATE('2021-01-01','YYYY-MM-DD'));
ALTER TABLE T_PART ADD PARTITION PMAX VALUES LESS THAN(MAXVALUE);

    1.2存在maxvalue的情况

CREATE TABLE T_PART_MAX(ID NUMBER PRIMARY KEY,
NAME VARCHAR2(20),
SALARY NUMBER,
S_TIME DATE)
PARTITION BY RANGE(S_TIME)
(
PARTITION P_1 VALUES LESS THAN (TO_DATE('2015-01-01','YYYY-MM-DD')), 
PARTITION P_2 VALUES LESS THAN (TO_DATE('2016-01-01','YYYY-MM-DD')), 
PARTITION P_3 VALUES LESS THAN (TO_DATE('2017-01-01','YYYY-MM-DD')), 
PARTITION P_4 VALUES LESS THAN (TO_DATE('2018-01-01','YYYY-MM-DD')), 
PARTITION P_5 VALUES LESS THAN (TO_DATE('2019-01-01','YYYY-MM-DD')), 
PARTITION P_6 VALUES LESS THAN (TO_DATE('2020-01-01','YYYY-MM-DD')),
PARTITION PMAX VALUES LESS THAN (MAXVALUE));


增加一个分区
ALTER TABLE T_PART_MAX SPLIT PARTITION PMAX AT(TO_DATE('2021-01-01','YYYY-MM-DD')) INTO (PARTITION P_7,PARTITION PMAX); 

2.删除一个分区

    2.1删除分区，删除数据
    ALTER TABLE T_PART DROP PARTITION P_7;

    2.2删除分区，保留数据
   具体方法见分区合并。

3.分区的合并

    3.1分区合并的正确方法

    新分区的下边界为原来边界值较低的分区，上边界为原来边界值较高的分区，原先的局部索引相应也会合并，全局索引会失效，需要rebuild。
    ALTER TABLE T_PART MERGE PARTITIONS P_1 ,P_2 INTO PARTITION P_1_2;

    3.2分区合并的注意事项

    合并分区的时候不能一次多个
    ALTER TABLE T_PART MERGE PARTITIONS P_3,P_4,P_5 INTO PARTITION P_3_4_5;
    ORA-14126: only a may follow description(s) of resulting partitions

    经过测试只能两两进行合并
    ALTER TABLE T_PART MERGE PARTITIONS P_3,P_4 INTO PARTITION P_3_4;
    ALTER TABLE T_PART MERGE PARTITIONS P_3_4,p_5 INTO PARTITION P_3_4_5;

4.分区的拆分

    4.1分区拆分正确方法

    ALTER TABLE T_PART SPLIT PARTITION P_1_2 AT(TO_DATE('2015-01-01','YYYY-MM-DD')) INTO (PARTITION P_1,PARTITION P_1_2);
    ALTER TABLE T_PART SPLIT PARTITION P_3_4_5 AT(TO_DATE('2017-01-01','YYYY-MM-DD')) INTO (PARTITION P_3,PARTITION P_3_4_5);
    ALTER TABLE T_PART SPLIT PARTITION P_3_4_5 AT(TO_DATE('2018-01-01','YYYY-MM-DD')) INTO (PARTITION P_4,PARTITION P_3_4_5);
    
    4.2分区拆分的时候，必须从小的开始，否则会提示如下报错：
    ALTER TABLE T_PART SPLIT PARTITION P_1_2 AT(TO_DATE('2016-01-01','YYYY-MM-DD')) INTO (PARTITION P_2,PARTITION P_1_2) 
    ORA-14080: partition cannot be split along the specified high bound

5.重命名分区名字

    Alter Table T_Part Rename Partition P_1_2 To P_2;
    ALTER TABLE T_PART RENAME PARTITION P_3_4_5 TO P_5;

6截断一个分区

    ALTER TABLE T_PART TRUNCATE PARTITION P_2;