exchange partition的一些測試

線上重定義表可以將一個普通錶轉換成一個分割槽表。
exchange partition可以將一個分割槽表的一個分割槽和另一張表的資料互換,
也可以從普通表表遷移至分割槽表.
它透過更新資料字典來實現分割槽表與普通表的轉換，所以速度非常快

SQL> create table LGX_PARTAB(
  2  A INT,
  3  B DATE)
  4  PARTITION BY RANGE(A)(
  5  PARTITION PART01 VALUES LESS THAN(10),
  6  PARTITION PART02 VALUES LESS THAN(20),
  7  PARTITION PART03 VALUES LESS THAN(MAXVALUE));

Table created.

SQL> INSERT INTO LGX_PARTAB VALUES(1,SYSDATE);

1 row created.

SQL> INSERT INTO LGX_PARTAB VALUES(11,SYSDATE);

1 row created.

SQL> SELECT * FROM LGX_PARTAB PARTITION (PART01);

         A B
---------- -------------------
         1 2015/09/28 09:35:32

SQL> SELECT * FROM LGX_PARTAB PARTITION (PART02);

         A B
---------- -------------------
        11 2015/09/28 09:35:39

SQL> CREATE TABLE LGX_TAB01(
  2  A INT,
  3  B DATE);

Table created.

SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
  2  WITH TABLE LGX_TAB01;

Table altered.

SQL> select * from lgx_tab01;

         A B
---------- -------------------
        11 2015/09/28 09:35:39

SQL> SELECT * FROM LGX_PARTAB PARTITION (PART02);

no rows selected

SQL> INSERT INTO LGX_TAB01 VALUES(12,SYSDATE);

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM LGX_TAB01;

         A B
---------- -------------------
        11 2015/09/28 09:35:39
        12 2015/09/28 09:37:50

SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
  2  WITH TABLE LGX_TAB01;

Table altered.

SQL> SELECT * FROM LGX_TAB01;

no rows selected

SQL> SELECT * FROM LGX_PARTAB PARTITION (PART02);

         A B
---------- -------------------
        11 2015/09/28 09:35:39
        12 2015/09/28 09:37:50

SQL> INSERT INTO LGX_TAB01 VALUES(30,SYSDATE);

1 row created.

SQL> commit;

Commit complete.

SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
  2  WITH TABLE LGX_TAB01;
WITH TABLE LGX_TAB01
           *
ERROR at line 2:
ORA-14099: all rows in table do not qualify for specified partition

SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
  2  WITH TABLE LGX_TAB01 WITHOUT VALIDATION;

Table altered.

SQL> SELECT * FROM LGX_PARTAB PARTITION (PART02);

         A B
---------- -------------------
        30 2015/09/28 09:39:46

SQL> SELECT * FROM LGX_TAB01;

         A B
---------- -------------------
        11 2015/09/28 09:35:39
        12 2015/09/28 09:37:50

SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
  2  WITH TABLE LGX_TAB01 WITH VALIDATION;

Table altered.

SQL> SELECT * FROM LGX_TAB01;

         A B
---------- -------------------
        30 2015/09/28 09:39:46

SQL> SELECT * FROM LGX_PARTAB PARTITION (PART02);

         A B
---------- -------------------
        11 2015/09/28 09:35:39
        12 2015/09/28 09:37:50

SQL> DESC LGX_TAB01;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(38)
 B                                                  DATE

SQL> DESC LGX_PARTAB;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(38)
 B                                                  DATE

SQL> alter table lgx_partab rename column b to c;

Table altered.

SQL> DESC LGX_PARTAB;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(38)
 C                                                  DATE

SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
  2  WITH TABLE LGX_TAB01;
WITH TABLE LGX_TAB01
           *
ERROR at line 2:
ORA-14099: all rows in table do not qualify for specified partition

SQL> delete from lgx_tab01;

1 row deleted.

SQL> commit;

Commit complete.

SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
  2  WITH TABLE LGX_TAB01;

Table altered.

SQL> SELECT * FROM LGX_TAB01;

         A B
---------- -------------------
        11 2015/09/28 09:35:39
        12 2015/09/28 09:37:50

SQL> SELECT * FROM LGX_PARTAB PARTITION (PART02);

no rows selected

SQL> DESC LGX_TAB01;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(38)
 B                                                  DATE

SQL> DESC LGX_PARTAB;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(38)
 C                                                  DATE

SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
  2  WITH TABLE LGX_TAB01;

Table altered.

SQL> SELECT * FROM LGX_PARTAB PARTITION (PART02);

         A C
---------- -------------------
        11 2015/09/28 09:35:39
        12 2015/09/28 09:37:50

SQL> SELECT * FROM LGX_TAB01;

no rows selected

SQL> ALTER TABLE LGX_TAB01 MODIFY (B VARCHAR2(30));

Table altered.

SQL> desc lgx_tab01;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(38)
 B                                                  VARCHAR2(30)

SQL> select * from lgx_tab01;

no rows selected

SQL> desc lgx_partab;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(38)
 C                                                  DATE

SQL> SELECT * FROM LGX_PARTAB PARTITION (PART02);

         A C
---------- -------------------
        11 2015/09/28 09:35:39
        12 2015/09/28 09:37:50

SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
  2  WITH TABLE LGX_TAB01;
ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
  2  WITH TABLE LGX_TAB01 WITH VALIDATION;
ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

SQL> ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
  2  WITH TABLE LGX_TAB01 WITHOUT VALIDATION;
ALTER TABLE LGX_PARTAB EXCHANGE PARTITION PART02
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

SQL>
注意： 　　
$涉及交換的兩表之間表結構必須一致，這裡的結構一致主要是指欄位數目必須相同而且資料型別必須相同，欄位的名字可以不同，如果
資料型別不同，即使加上without validation也沒有用，without validation的功能主要是用來驗證資料的。 　　
$如果是從非分割槽表向分割槽表做交換，非分割槽表中的資料必須符合分割槽表中指定分割槽的規則，除非附加without validation子句; 　　
$如果從分割槽表向分割槽表做交換，被交換的分割槽的資料必須符合分割槽規則，除非附加without validation子句;
$Global索引或涉及到資料改動了的global索引分割槽會被置為unusable，除非附加update indexes子句。