列表分割槽ADD VALUES或DROP VALUES包含資料變化的情況

在介紹ADD VALUES和DROP VALUES語句的時候提到過，ADD VALUES和DROP VALUES只是資料字典上的變更，並不涉及資料的變化。因此如果ADD VALUES或DROP VALUES語句執行時，新增或刪除的鍵值在資料庫中已經存在，則會報錯。

列表分割槽的ADD VALUES和DROP VALUES語句：http://yangtingkun.itpub.net/post/468/497392

 

 

仍然借用上一篇文章中的例子：

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操作。