Oracle 12C R2-新特性-分区：分区交换的表创建

新的DDL命令允许创建与分区表的结构完全相同的表，因此可以进行分区表的分区或子分区交换。请注意，索引不是作为此命令的一部分创建的。

可以使用FOR EXCHANGE WITH子句创建表，以与分区表的形状完全匹配，并且有资格执行分区交换命令，但不会创建相应的索引。

以下列表是使用DDL操作的CREATE TABLE FOR EXCHANGE的效果的摘要：

·此DDL操作的用例是便于创建要用于交换分区DDL的表。

·操作将按列排序和列属性创建for exchange表的克隆。

·无法重命名列。正在创建的表继承了for exchange表的名称。

·在DDL操作期间可以指定的唯一逻辑属性是表的分区规范。

分区子句仅与与复合分区表的分区的交换相关。在这种情况下，具有n个子分区的分区与具有匹配子分区的n个分区的分区表交换。您在此场景中负责此交换的分区子句的定义。

子分区可以是跨分区的不对称。分区子句必须完全匹配要交换的分区的子分区。

·可以指定的物理属性主要是表段属性。

·使用此DDL操作复制的列属性包括但不限于以下内容：不可用列，不可见列，虚拟表达式列，功能索引表达式列以及其他内部设置和属性。

1 创建一个分区表

SQL> create table t_test (

  pk_id           number(30)  not null,

  add_date_time      DATE,

  constraint PK_T_TEST primary key (pk_id))

PARTITION BY RANGE (add_date_time)

(PARTITION t_test_2013_less VALUES LESS THAN (TO_DATE('2013-01-01 00:00:00','yyyy-mm-ddh  2    3    4    5    6  h24:mi:ss')) TABLESPACE tbs_1,

PARTITION t_test_2013 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE tbs_1,

PARTITION t_test_2014 VALUES LESS THAN (TO_DATE('2015-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs_1  7    8  ,

PARTITION t_test_2015 VALUES LESS THAN (TO_DATE('2016-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs_1);  9  

Table created.

2 查看分区表信息

SQL> Select table_name,partition_name,high_value from dba_tab_partitions where table_name='T_TEST';

 

TABLE_NAME	PARTITION_NAME	       HIGH_VALUE

--------------- ------------------------------ --------------------------------------------------------------------------------

T_TEST	T_TEST_2013	       TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_TEST	T_TEST_2013_LESS	       TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_TEST	T_TEST_2014	       TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_TEST	T_TEST_2015	       TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

3 使用FOR EXCHANGE WITH字句创建表

SQL> CREATE TABLE t_test_exchange

  TABLESPACE tbs_1

  FOR EXCHANGE WITH TABLE t_test;  2    3  

 

Table created.

SQL> desc T_TEST_EXCHANGE

 Name	  Null?            Type

 ------------------------------------  -----------------------------  --------------------

 PK_ID	          NOT NULL      NUMBER(30)

 ADD_DATE_TIME	          DATE

3.1 插入一些数据，用于验证交换分区

SQL> insert into T_TEST_EXCHANGE values(666,sysdate-700);

 

1 row created.

SQL> select * from T_TEST_EXCHANGE;

 

     PK_ID ADD_DATE_TIME

---------- ------------------

       666 2015:03:2415:53:51

4 交换一个Range，Hash，List分区

SQL> ALTER TABLE T_TEST EXCHANGE PARTITION T_TEST_2015 WITH TABLE T_TEST_EXCHANGE;

ALTER TABLE T_TEST EXCHANGE PARTITION T_TEST_2015 WITH TABLE T_TEST_EXCHANGE

*

ERROR at line 1:

ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION

这里报错了，原因是T_TEST_EXCHANGE没有对应的主键

SQL> col CONSTRAINT_NAME for a20

SQL> col TABLE_NAME for a20

SQL> col COLUMN_NAME for a20

SQL> select cu.CONSTRAINT_NAME,cu.TABLE_NAME,cu.COLUMN_NAME from user_cons_columns cu, user_constraints au

where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 'T_TEST';  2  

 

no rows selected

解决方法两种：

1. 把分区表的索引删了，交换完之后再建。

2. 在待交换的表上创建索引。

使用第二种

SQL> alter table T_TEST_EXCHANGE add constraint PK_T_TEST_EXCHANGE primary key(PK_ID);

Table altered.

SQL> select cu.CONSTRAINT_NAME,cu.TABLE_NAME,cu.COLUMN_NAME from user_cons_columns cu, user_constraints au

where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 'T_TEST_EXCHANGE';  2  

 

CONSTRAINT_NAME      TABLE_NAME   COLUMN_NAME

-------------------- -------------------- --------------------

PK_T_TEST_EXCHANGE   T_TEST_EXCHANGE	  PK_ID

4.1 再次尝试交换分区

SQL> ALTER TABLE T_TEST EXCHANGE PARTITION T_TEST_2015 WITH TABLE T_TEST_EXCHANGE;
 

Table altered.

成功了，查看数据是否存在。

SQL> select * from T_TEST;

     PK_ID ADD_DATE_TIME

---------- ------------------

       666 2015:03:2415:53:51   --数据和前面插入的保持一致。

交换完毕，源表数据被清空

SQL> SELECT * FROM T_TEST_EXCHANGE;

no rows selected

分区交换的表创建

签到成功

CNDBA社区