Automatically create partitions under Oracle 11g

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;