before 11g, maintaining the partition requires manual. After 11g, interval is used to automate the expansion of partitions, simplifying maintenance.
According to annual INTERVAL (Numtoyminterval (1, ' Year '))
according to Month INTERVAL (Numtoyminterval (1, ' month '))
According to Days INTERVAL (Numtodsinterval (1, ' Day '))
Here's an experiment with auto-scaling by Month:
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 ')
);
--see only one partition
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;
--you can see that the partition at the beginning of Sys is automatically extended
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
--and look at the table structure of T_range
create table T_range
(
ID number not null,< br> test_date DATE
)
Partition by Range (test_date)
(
partition p_2014_01_01 values less than (T O_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);
-If the partition name is not very good, you can modify it yourself:
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;