oracle范围分区合并,Oracle范围分区表和INTERVAL分区表相互转化

INTERVAL分区其实是一种比较特殊的范围分区，因此可以很方便的将RANGE分区表转化为INTERVAL分区表，同样可以将INTERVAL分区表转化为RANGE分区表。

对于一个普通的范围分区表：

SQL> CREATE TABLE T_PART

2  (ID NUMBER,

3  NAME VARCHAR2(30),

4  CREATE_DATE DATE)

5  PARTITION BY RANGE (ID)

6  (PARTITION P1 VALUES LESS THAN (100),

7  PARTITION P2 VALUES LESS THAN (200),

8  PARTITION P3 VALUES LESS THAN (300));

表已创建。

SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, INTERVAL

2  FROM USER_PART_TABLES

3  WHERE TABLE_NAME = 'T_PART';

TABLE_NAME                     PARTITION INTERVAL

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

T_PART                         RANGE

SQL> INSERT INTO T_PART

2  VALUES (100, 'A', SYSDATE);

已创建1行。

SQL> INSERT INTO T_PART

2  VALUES (240, 'A', SYSDATE);

已创建1行。

SQL> INSERT INTO T_PART

2  VALUES (360, 'TEST', SYSDATE);

INSERT INTO T_PART

*

第1行出现错误:

ORA-14400:插入的分区关键字未映射到任何分区

插入超过分区上限的记录会报错，下面通过一个简单ALTER TABLE语句，将RANGE分区表转化为INTERVAL分区表：

SQL> ALTER TABLE T_PART SET INTERVAL (100);

表已更改。

SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, INTERVAL

2  FROM USER_PART_TABLES

3  WHERE TABLE_NAME = 'T_PART';

TABLE_NAME                     PARTITION INTERVAL

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

T_PART                         RANGE     100

SQL> INSERT INTO T_PART

2  VALUES (360, 'TEST', SYSDATE);

已创建1行。

SQL> COMMIT;

提交完成。

SQL> SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE

2  FROM USER_TAB_PARTITIONS

3  WHERE TABLE_NAME = 'T_PART';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE

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

T_PART                         P1                             100

T_PART                         P2                             200

T_PART                         P3                             300

T_PART                         SYS_P97                        400

对于INTERVAL分区表，新增的超过分区上限的数据会自动导致对应的INTERVAL分区被建立。

同样INTERVAL分区表可以方便的转化为RANGE分区表，只需要不输入INTERVAL的值即可：

SQL> ALTER TABLE T_PART SET INTERVAL ();

表已更改。

SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, INTERVAL

2  FROM USER_PART_TABLES

3  WHERE TABLE_NAME = 'T_PART';

TABLE_NAME                     PARTITION INTERVAL

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

T_PART                         RANGE

SQL> INSERT INTO T_PART

2  VALUES (450, 'B', SYSDATE);

INSERT INTO T_PART

*

第1行出现错误:

ORA-14400:插入的分区关键字未映射到任何分区

同时需要注意，不输入INTERVAL的值不等价于输入NULL：

SQL> ALTER TABLE T_PART SET INTERVAL (NULL);

ALTER TABLE T_PART SET INTERVAL (NULL)

*

第1行出现错误:

ORA-14752:间隔表达式不是正确类型的常数

除了RANGE分区表和INTERVAL分区表之间可以相互转化，INTERVAL分区也可以转化为RANGE分区：

SQL> ALTER TABLE T_PART SET INTERVAL (100);

表已更改。

SQL> SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE, INTERVAL

2  FROM USER_TAB_PARTITIONS

3  WHERE TABLE_NAME = 'T_PART';

TABLE_NAME PARTITION_NAME  HIGH_VALUE                               INTERVAL

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

T_PART     P1              100                                      NO

T_PART     P2              200                                      NO

T_PART     P3              300                                      NO

T_PART     SYS_P97         400                                      NO

SQL> INSERT INTO T_PART

2  VALUES (453, 'A', SYSDATE);

已创建1行。

SQL> INSERT INTO T_PART

2  VALUES (743, 'B', SYSDATE);

已创建1行。

SQL> COMMIT;

提交完成。

SQL> SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE, INTERVAL

2  FROM USER_TAB_PARTITIONS

3  WHERE TABLE_NAME = 'T_PART';

TABLE_NAME PARTITION_NAME  HIGH_VALUE                               INTERVAL

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

T_PART     P1              100                                      NO

T_PART     P2              200                                      NO

T_PART     P3              300                                      NO

T_PART     SYS_P97         400                                      NO

T_PART     SYS_P98         500                                      YES

T_PART     SYS_P99         800                                      YES

已选择6行。

除了MERGE PARTITION、SPLIT PARTITION之外，直接修改分区表为RANGE分区，也会使得所有现存的INTERVAL分区变成RANGE分区：

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'T_PART')

2  FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T_PART')

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

CREATE TABLE "YANGTK"."T_PART"

(    "ID" NUMBER,

"NAME" VARCHAR2(30),

"CREATE_DATE" DATE

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "YANGTK"

PARTITION BY RANGE ("ID") INTERVAL (100)

(PARTITION "P1"  VALUES LESS THAN (100)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE

DEFAULT)

TABLESPACE "YANGTK" NOCOMPRESS ,

PARTITION "P2"  VALUES LESS THAN (200)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "YANGTK" NOCOMPRESS ,

PARTITION "P3"  VALUES LESS THAN (300)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFA

ULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "YANGTK" NOCOMPRESS ,

PARTITION "SYS_P97"  VALUES LESS THAN (400)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT C

ELL_FLASH_CACHE DEFAULT)

TABLESPACE "YANGTK" NOCOMPRESS )

SQL> ALTER TABLE T_PART SET INTERVAL ();

表已更改。

SQL> SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE, INTERVAL

2  FROM USER_TAB_PARTITIONS

3  WHERE TABLE_NAME = 'T_PART';

TABLE_NAME PARTITION_NAME  HIGH_VALUE                               INTERVAL

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

T_PART     P1              100                                      NO

T_PART     P2              200                                      NO

T_PART     P3              300                                      NO

T_PART     SYS_P97         400                                      NO

T_PART     SYS_P98         500                                      NO

T_PART     SYS_P99         800                                      NO

已选择6行。

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'T_PART')

2  FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T_PART')

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

CREATE TABLE "YANGTK"."T_PART"

(    "ID" NUMBER,

"NAME" VARCHAR2(30),

"CREATE_DATE" DATE

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "YANGTK"

PARTITION BY RANGE ("ID")

(PARTITION "P1"  VALUES LESS THAN (100)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "YANGTK" NOCOMPRESS ,

PARTITION "P2"  VALUES LESS THAN (200)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT C

ELL_FLASH_CACHE DEFAULT)

TABLESPACE "YANGTK" NOCOMPRESS ,

PARTITION "P3"  VALUES LESS THAN (300)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_

CACHE DEFAULT)

TABLESPACE "YANGTK" NOCOMPRESS ,

PARTITION "SYS_P97"  VALUES LESS THAN (400)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE

DEFAULT)

TABLESPACE "YANGTK" NOCOMPRESS ,

PARTITION "SYS_P98"  VALUES LESS THAN (500)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFA

ULT)

TABLESPACE "YANGTK" NOCOMPRESS ,

PARTITION "SYS_P99"  VALUES LESS THAN (800)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "YANGTK" NOCOMPRESS )

需要注意，INTERVAL分区在DBMS_METADATA获取的表结构中并不会出现，一旦转化为RANGE分区，则DBMS_METADATA获取的源数据会包括分区信息。