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
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

ORACLE 11g 範圍分割槽錶轉換INTERVAL分割槽表

相關文章