Oracle 11g下自动创建分区

  11g之前，维护分区需要手工。11g之后使用interval来实现自动扩展分区，简化了维护。

   根据年:  INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))

   根据月:  INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))

   根据天:  INTERVAL(NUMTODSINTERVAL(1,'DAY'))

   根据时分秒: NUMTODSINTERVAL( n, { 'DAY'|'HOUR'|'MINUTE'|'SECOND'})

  下面用按月自动扩展来做个试验：

SQL> create table t_range (id number not null PRIMARY KEY, test_date date)
      partition by range (test_date) interval (numtoyMinterval (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_random.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_DATE)
(
  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', 
  'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition SYS_P22 values less than (TO_DATE(' 2014-03-01 00:00:00', 
  'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition SYS_P23 values less than (TO_DATE(' 2014-04-01 00:00:00', 
  'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_RANGE
  add primary key (ID);

--如果对分区名不太爽，则可以自己修改一下：

alter table t_range rename partition SYS_P21 to p_2014_02_01;
alter table t_range rename partition SYS_P22 to p_2014_03_01;
alter table t_range rename partition SYS_P23 to p_2014_04_01;

自动分区的限制，摘自官方文档：

Restrictions on Interval Partitioning The INTERVAL clause is subject to the restrictions listed in "Restrictions on Partitioning in General" and "Restrictions on Range Partitioning". The following additional restrictions apply:

• You can specify only one partitioning key column, and it must be of NUMBER, DATE, FLOAT, or TIMESTAMP data type.

• This clause is not supported for index-organized tables.

• This clause is not supported for tables containing nested table columns or varray columns.

• You cannot create a domain index on an interval-partitioned table.

• Interval partitioning is not supported at the subpartition level.

• Serializable transactions do not work with interval partitioning. Trying to insert data into a partition of an interval partitioned table that does not yet have a segment causes an error.

• In the VALUES clause:

  • You cannot specify MAXVALUE (an infinite upper bound), because doing so would defeat the purpose of the automatic addition of partitions as needed.

  • You cannot specify NULL values for the partitioning key column.