11g之前,维护分区需要手工。11g之后使用interval来实现自动扩展分区,简化了维护。 根据年: INTERVAL(NUMTO
ymINTERVAL(1,'YEAR')) 根据月: INTERVAL(NUMTO
ymINTERVAL(1,'MONTH')) 根据天: INTERVAL(NUMTODSINTERVAL(1,'DAY')) 根据时分秒: NUMTODSINTERVAL( n,
{ 'DAY'|'HOUR'|'
minutE'|'SECOND'}) 下面用按月自动扩展来做个试验: sql>
create table t_range (
id number not null PRI
Mary KEY,
test_date dat
E) partition by range (
test_dat
E) interval (numto
yminterval (1,'MONTH')) ( partition p_2014_01_01 values less than (to_date('2014-01-01','yyyy-mm-dd'))
); --看到只有一个分区 sql>
SELEct
partition_name from
user_tab_partitions where table_name='T_RANGE';
partition_name ------------------------------ P_2014_01_01 sql> insert /*+append */ into t_range
Select ROWNUM,to_date(
TO_CHAR(
sysdate - 140,'J') + trunc(
DBMS_rando
m.value(0,80)),'J') from dual connect by rownum <= 100000; sql> commit; --可以看到SYS开头的分区是自动扩展的 sql>
SELEct
partition_name from
user_tab_partitions where table_name='T_RANGE';
partition_name ------------------------------ P_2014_01_01 SYS_P21 SYS_P22 SYS_P23 --再看看t_range的表结构
create table T_RANGE (
id number not null,
test_DATE DATE ) partition by range (
test_DAT
E) ( partition P_2014_01_01 values less than (TO_DATE(' 2014-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')),partition SYS_P21 values less than (TO_DATE(' 2014-02-01 00:00:00',partition SYS_P22 values less than (TO_DATE(' 2014-03-01 00:00:00',partition SYS_P23 values less than (TO_DATE(' 2014-04-01 00:00:00','NLS_CALENDAR=GREGORIAN'))
); -- Create/Recreate pri
Mary,unique and foreign key cons
Traints
alter table T_RANGE add
primary key (id); --如果
对分区名不满意,则可以自己修改一下:
alter table t_range r
ename partition SYS_P21 to p_2014_02_01;
alter table t_range r
ename partition SYS_P22 to p_2014_03_01;
alter table t_range r
ename partition SYS_P23 to p_2014_04_01;