ORACLE 11g 范围分区表转换INTERVAL分区表

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

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

1. 创建分区表
CREATE TABLE T_PART3(ID NUMBER,NAME VARCHAR2(30),CREATE_DATE TIMESTAMP)
PARTITION BY RANGE (CREATE_DATE)
(PARTITION P3_1 VALUES LESS THAN (TIMESTAMP' 2016-01-01 00:00:00') ,
PARTITION P3_2 VALUES LESS THAN (TIMESTAMP' 2016-02-01 00:00:00') ,
PARTITION P3_3 VALUES LESS THAN (TIMESTAMP' 2016-03-01 00:00:00')
);

2. 随机插入数据

insert into T_PART3
select rownum*10,'N'||level,sysdate+dbms_random.value(0,250) from dual connect by level <1000;

--超出分区范围
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

--修改语句，继续插入

insert into T_PART3
select rownum*10,'N'||level,sysdate+dbms_random.value(0,50) from dual connect by level <1000;
commit;
exec dbms_stats.gather_table_stats('','T_PART3');
PL/SQL procedure successfully completed.
3. 查看分区数据分部情况
select table_owner,table_name,partition_name,partition_position,interval,num_rows, HIGH_VALUE from dba_tab_partitions where table_name = 'T_PART3';
TABLE_OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION INTERV NUM_ROWS HIGH_VALUE
-------------------- -------------------------------- -------------------- ------------------ ------ ---------- -----------------------------------
IBM T_PART3 P3_1 1 NO 0 TIMESTAMP' 2016-01-01 00:00:00'
IBM T_PART3 P3_2 2 NO 452 TIMESTAMP' 2016-02-01 00:00:00'
IBM T_PART3 P3_3 3 NO 547 TIMESTAMP' 2016-03-01 00:00:00'
4. 转换为按月的INTERVAL分区表
ALTER TABLE T_PART3 SET INTERVAL (NUMTOYMINTERVAL (1,'MONTH'));

5. 加大范围，插入数据

insert into T_PART3
select rownum*10,'N'||level,sysdate+dbms_random.value(0,250) from dual connect by level <2000;

6. 查看分区数据分部情况
exec dbms_stats.gather_table_stats('','T_PART3');

select table_owner,table_name,partition_name,partition_position,interval,num_rows, HIGH_VALUE from dba_tab_partitions where table_name = 'T_PART3';
TABLE_OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION INTERV NUM_ROWS HIGH_VALUE
-------------------- -------------------------------- -------------------- ------------------ ------ ---------- -----------------------------------
IBM T_PART3 P3_1 1 NO 0 TIMESTAMP' 2016-01-01 00:00:00'
IBM T_PART3 P3_2 2 NO 650 TIMESTAMP' 2016-02-01 00:00:00'
IBM T_PART3 P3_3 3 NO 760 TIMESTAMP' 2016-03-01 00:00:00'
IBM T_PART3 SYS_P53133 4 YES 245 TIMESTAMP' 2016-04-01 00:00:00'
IBM T_PART3 SYS_P53134 5 YES 267 TIMESTAMP' 2016-05-01 00:00:00'
IBM T_PART3 SYS_P53130 6 YES 261 TIMESTAMP' 2016-06-01 00:00:00'
IBM T_PART3 SYS_P53132 7 YES 243 TIMESTAMP' 2016-07-01 00:00:00'
IBM T_PART3 SYS_P53131 8 YES 239 TIMESTAMP' 2016-08-01 00:00:00'
IBM T_PART3 SYS_P53135 9 YES 238 TIMESTAMP' 2016-09-01 00:00:00'
IBM T_PART3 SYS_P53136 10 YES 95 TIMESTAMP' 2016-10-01 00:00:00'

7. INTERVAL分区表转换未正常的RANGE分区
ALTER TABLE T_PART3 SET INTERVAL ();

若分区表存在maxvalue分区，则不能直接转换为INTERVAL分区，需要先将maxvalue分区删除才可转换。

1. 创建存在maxvalue分区的分区表
CREATE TABLE T_PART4(ID NUMBER,NAME VARCHAR2(30),CREATE_DATE TIMESTAMP)
PARTITION BY RANGE (CREATE_DATE)
(PARTITION P4_1 VALUES LESS THAN (TIMESTAMP' 2016-01-01 00:00:00') ,
PARTITION P4_2 VALUES LESS THAN (TIMESTAMP' 2016-02-01 00:00:00') ,
PARTITION P4_3 VALUES LESS THAN (TIMESTAMP' 2016-03-01 00:00:00') ,
PARTITION P4_4 VALUES LESS THAN (maxvalue)
);
insert into T_PART4
select rownum*10,'N'||level,sysdate+dbms_random.value(0,250) from dual connect by level <1040;
exec dbms_stats.gather_table_stats('','T_PART4');
2. 查询分区数据分部情况
select table_owner,table_name,partition_name,partition_position,interval,num_rows, HIGH_VALUE from dba_tab_partitions where table_name = 'T_PART4';
TABLE_OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION INTERV NUM_ROWS HIGH_VALUE
-------------------- -------------------------------- -------------------- ------------------ ------ ---------- -----------------------------------
IBM T_PART4 P4_1 1 NO 0 TIMESTAMP' 2016-01-01 00:00:00'
IBM T_PART4 P4_2 2 NO 100 TIMESTAMP' 2016-02-01 00:00:00'
IBM T_PART4 P4_3 3 NO 126 TIMESTAMP' 2016-03-01 00:00:00'
IBM T_PART4 P4_4 4 NO 813 MAXVALUE
3. 转换为按月的INTERVAL分区表
ALTER TABLE T_PART4 SET INTERVAL (NUMTOYMINTERVAL (1,'MONTH'));

ERROR at line 1:
ORA-14759: SET INTERVAL is not legal on this table.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Message:
ORA-14759: SET INTERVAL is not legal on this table.

Cause:
ALTER TABLE SET INTERVAL is only legal on a range partitioned table with a single partitioning column. Additionally this table cannot have a maxvalue partition.

Action:
Use SET INTERVAL only on a valid table
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

ITPub博客

ORACLE 11g 范围分区表转换INTERVAL分区表

最新文章