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

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

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

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

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

 2. 随机插入数据

1. insert into T_PART3
   
2. 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
 
--修改语句，继续插入

1. insert into T_PART3 
2. select rownum*10,'N'||level,sysdate+dbms_random.value(0,50) from dual connect by level <1000;
   
   
3. commit;
   
4. 
   
5. exec dbms_stats.gather_table_stats('','T_PART3');
   
6. 
   
7. PL/SQL procedure successfully completed.
   
8. 
   
9. 3. 查看分区数据分部情况
10. select table_owner,table_name,partition_name,partition_position,interval,num_rows, HIGH_VALUE from dba_tab_partitions where table_name = 'T_PART3';
    
11. 
    
12. 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'
    
    
13. 4. 转换为按月的INTERVAL分区表
14. ALTER TABLE T_PART3 SET INTERVAL (NUMTOYMINTERVAL (1,'MONTH'));

  5. 加大范围，插入数据

1. insert into T_PART3 
2. 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');

1. select table_owner,table_name,partition_name,partition_position,interval,num_rows, HIGH_VALUE from dba_tab_partitions where table_name = 'T_PART3';
   
2. 
   
3. 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'
   

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

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

1. 1. 创建存在maxvalue分区的分区表
2. CREATE TABLE T_PART4(ID NUMBER,NAME VARCHAR2(30),CREATE_DATE TIMESTAMP)
   
3.   PARTITION BY RANGE (CREATE_DATE)
   
4. (PARTITION P4_1 VALUES LESS THAN (TIMESTAMP' 2016-01-01 00:00:00') ,
   
5.  PARTITION P4_2 VALUES LESS THAN (TIMESTAMP' 2016-02-01 00:00:00') ,
   
6.  PARTITION P4_3 VALUES LESS THAN (TIMESTAMP' 2016-03-01 00:00:00') ,
   
7.  PARTITION P4_4 VALUES LESS THAN (maxvalue)
   
8.  );
   
9.  
   
10. insert into T_PART4
    
11. select rownum*10,'N'||level,sysdate+dbms_random.value(0,250) from dual connect by level <1040;
    
12. 
    
13. 
    
14. exec dbms_stats.gather_table_stats('','T_PART4');
    
15. 
    
16. 2. 查询分区数据分部情况
17. select table_owner,table_name,partition_name,partition_position,interval,num_rows, HIGH_VALUE from dba_tab_partitions where table_name = 'T_PART4';
    
18. 
    
19. 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
    
    
20. 3. 转换为按月的INTERVAL分区表
21. 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博客 ” ，链接：http://blog.itpub.net/17086096/viewspace-1973998/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17086096/viewspace-1973998/