oracle 表分区教程

一、按年分区，一年一个分区

create table GRMC\_AWST\_ELE ( iiiii VARCHAR2(100), beijingtime1 DATE ) partition by range (beijingtime1) ( partition P2013 values less than(to\_date('2014-01-01','yyyy-mm-dd')), partition P2014 values less than(to\_date('2015-01-01','yyyy-mm-dd')), partition P2015 values less than(to\_date('2016-01-01','yyyy-mm-dd')), partition P2016 values less than(to\_date('2017-01-01','yyyy-mm-dd')), partition P2017 values less than(to\_date('2018-01-01','yyyy-mm-dd')), partition P2018 values less than(to\_date('2019-01-01','yyyy-mm-dd')), partition P2019 values less than(to\_date('2020-01-01','yyyy-mm-dd')), partition PALL values less than(maxvalue) ) 二、按月自动分区，2014-5-1为一个分区，以后每个月一个分区 create TABLE TABLE1 ( TABLE\_ID NUMBER(8), SUB\_DATE DATE ) PARTITION BY RANGE(SUB\_DATE) INTERVAL(NUMTOYMINTERVAL(1，'MONTH')) ( PARTITION P1 VALUES LESS THAN(TO\_DATE('2017-05-01','YYYY-MM-DD')) ); 测试： insert into TABLE1(TABLE\_ID,SUB\_DATE) values(1,sysdate) insert into TABLE1(TABLE\_ID,SUB\_DATE) values(2,to\_date('2017-01-01','yyyy-mm-dd')); insert into TABLE1(TABLE\_ID,SUB\_DATE) values(2,to\_date('2017-07-01','yyyy-mm-dd')); SELECT * FROM dba\_segments t WHERE t.segment\_name ='TABLE1'; 查看分区表空间大小 SELECT * FROM dba\_segments t WHERE t.segment\_name ='TABLE1'; 备注：11g之前，维护分区需要手工。11g之后使用interval来实现自动扩展分区，简化了维护。

*根据年: INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
根据月: INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
根据天: INTERVAL(NUMTODSINTERVAL(1,'DAY'))*