列表分区ADD VALUES或DROP VALUES包含数据变化的情况

在介绍ADD VALUES和DROP VALUES语句的时候提到过，ADD VALUES和DROP VALUES只是数据字典上的变更，并不涉及数据的变化。因此如果ADD VALUES或DROP VALUES语句执行时，新增或删除的键值在数据库中已经存在，则会报错。
仍然借用上一篇文章中的例子：

SQL> CREATE TABLE T_PART_LIST 
  2  (
  3     OWNER VARCHAR2(30), 
  4     NAME VARCHAR2(30), 
  5     TABLESPACE_NAME VARCHAR2(30), 
  6     TYPE VARCHAR2(18)
  7  )
  8  PARTITION BY LIST (TABLESPACE_NAME)
  9  (
 10  PARTITION P1 VALUES ('SYSTEM'), 
 11  PARTITION P2 VALUES ('YANGTK'),
 12  PARTITION P3 VALUES ('USERS'), 
 13  PARTITION P4 VALUES (DEFAULT)
 14  );
表已创建。
SQL> INSERT INTO T_PART_LIST 
  2  SELECT OWNER, SEGMENT_NAME, TABLESPACE_NAME, SEGMENT_TYPE
  3  FROM DBA_SEGMENTS;
已创建5628行。
SQL> COMMIT;
提交完成。

一般来说，我们不会执行下面的这种SQL：

SQL> ALTER TABLE T_PART_LIST
  2  MODIFY PARTITION P2
  3  ADD VALUES ('USERS');
ALTER TABLE T_PART_LIST
*
第 1 行出现错误:
ORA-14312: 值 'USERS' 已经存在于分区 3 中

显然键值’USERS’对应的是另一个分区，这时只需要进行MERGE PARTITIONS操作就可以了：

SQL> ALTER TABLE T_PART_LIST
  2  MERGE PARTITIONS P2, P3
  3  INTO PARTITION P2;
表已更改。
SQL> COL TABLE_NAME FORMAT A15
SQL> COL PARTITION_NAME FORMAT A15
SQL> COL HIGH_VALUE FORMAT A30
SQL> SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
  2  FROM USER_TAB_PARTITIONS
  3  WHERE TABLE_NAME = 'T_PART_LIST';
TABLE_NAME      PARTITION_NAME  HIGH_VALUE
--------------- --------------- ------------------------------
T_PART_LIST     P1              'SYSTEM'
T_PART_LIST     P2              'USERS', 'YANGTK'
T_PART_LIST     P4              DEFAULT

这种ADD VALUES的需求很容易解决。更容易出现的需求类型下面的SQL：

SQL> ALTER TABLE T_PART_LIST
  2  MODIFY PARTITION P1
  3  ADD VALUES ('SYSAUX');
ALTER TABLE T_PART_LIST
            *
第 1 行出现错误:
ORA-14324: 所要添加的值已存在于 DEFAULT 分区之中

SQL> SELECT DISTINCT TABLESPACE_NAME
  2  FROM T_PART_LIST PARTITION (P4);
TABLESPACE_NAME
------------------------------
SYSAUX
UNDOTBS1

对于这种情况，就没有办法使用一个SQL来完成操作了，需要先对DEFAULT分区进行SPLIT，然后再进行MERGE：

SQL> ALTER TABLE T_PART_LIST
  2  SPLIT PARTITION FOR('SYSAUX') 
  3  VALUES ('SYSAUX')
  4  INTO (PARTITION P3, PARTITION P4);
表已更改。
SQL> ALTER TABLE T_PART_LIST
  2  MERGE PARTITIONS FOR('SYSTEM'), FOR('SYSAUX')
  3  INTO PARTITION P1;
表已更改。
SQL> SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
  2  FROM USER_TAB_PARTITIONS
  3  WHERE TABLE_NAME = 'T_PART_LIST';
TABLE_NAME      PARTITION_NAME  HIGH_VALUE
--------------- --------------- ------------------------------
T_PART_LIST     P1              'SYSAUX', 'SYSTEM'
T_PART_LIST     P2              'USERS', 'YANGTK'
T_PART_LIST     P4              DEFAULT

同样，DROP VALUES对于包含数据的情况也需要两个步骤：

SQL> ALTER TABLE T_PART_LIST 
  2  MODIFY PARTITION FOR('USERS')
  3  DROP VALUES ('USERS');
ALTER TABLE T_PART_LIST
            *
第 1 行出现错误:
ORA-14518: 分区包含的某些行对应于已删除的值

SQL> ALTER TABLE T_PART_LIST
  2  SPLIT PARTITION FOR ('USERS')
  3  VALUES ('USERS') 
  4  INTO (PARTITION P3, PARTITION P2);
表已更改。
SQL> ALTER TABLE T_PART_LIST
  2  MERGE PARTITIONS FOR('USERS'), FOR('THE OTHERS')
  3  INTO PARTITION P4;
表已更改。
SQL> SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
  2  FROM USER_TAB_PARTITIONS
  3  WHERE TABLE_NAME = 'T_PART_LIST';
TABLE_NAME      PARTITION_NAME  HIGH_VALUE
--------------- --------------- ------------------------------
T_PART_LIST     P1              'SYSAUX', 'SYSTEM'
T_PART_LIST     P2              'YANGTK'
T_PART_LIST     P4              DEFAULT

当然这里说的DROP VALUES的操作是指将’USERS’键值从分区P2中去掉，而对应的数据需要回到DEFAULT分区中，并不是要删除这部分的数据。
如果要删除数据，那么有两个不同的方法，一个方法就是用DELETE语句直接删除对应的数据，然后再利用DROP VALUES语句清除分区上的定义。这种方面的缺点是包含DML操作，会产生大量的REDO和UNDO，只适用于数据量不大的情况。另外一个方法仍然是先进行SPLIT分区的操作，然后第二步的使用使用DROP PARTITION代替MERGE PARTITIONS操作。