Oracle11g下自動創建分區

11g之前，維護分區需要手工。11g之後使用interval來實現自動擴展分區，簡化了維護。

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

根據天: 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.