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子句。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1810599/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1810599/