How to automatically create an Oracle 11g partition table by Time

11g new Features _ partitioned tables are automatically created by time (Interval partitioning (MONTH, year)

Interval Partitioning (MONTH, year)

CREATE TABLE Sales6

(

SALES_ID number,

Sales_dt Date

)

Partition by range (SALES_DT)

Interval (numtoyminterval (1, ' MONTH '))

(

Partition p0701 values less than (to_date (' 2007-02-01 ', ' yyyy-mm-dd '))

);

Sql>insert into SALES6 values (1, ' 01-jun-07 ');

1 row created.

Sql> Select Partition_name, high_value from user_tab_partitions where table_name = ' SALES6 ';

Partition_name High_value

-------------------- ----------------------------------------

P0701 to_date (' 2007-02-01 00:00:00 ', ' syyyy-m

M-dd HH24:MI:SS ', ' Nls_calendar=gregoria

Sys_p23 to_date (' 2007-07-01 00:00:00 ', ' syyyy-m

M-dd HH24:MI:SS ', ' Nls_calendar=gregoria

Note that the new partition sys_p42, with an upper limit of June 1, allows the partition to retain May 2006 data. The partition was created by splitting the sys_p41 partition (for June). Therefore, when you define an interval partitioning scheme, Oracle automatically creates and maintains partitions.

More Wonderful content: http://www.bianceng.cn/database/Oracle/

If you want to store the partition in a particular tablespace, you can do so by using the store in clause:

Interval (numtoyminterval (1, ' MONTH '))

Store in (par01,par02,par03,par04,par05,par06,par07,par08,par09,par10,par011,par012)

This clause stores the partitions in the Tablespace TS1, TS2, and TS3 in a circular manner.

Such as:

Create tablespace par01 datafile ' +dgdatga ' size 10m;

Create tablespace par02 datafile ' +dgdatga ' size 10m;

Create tablespace par03 datafile ' +dgdatga ' size 10m;

Create tablespace par04 datafile ' +dgdatga ' size 10m;

Create tablespace par05 datafile ' +dgdatga ' size 10m;

Create tablespace par06 datafile ' +dgdatga ' size 10m;

Create tablespace par07 datafile ' +dgdatga ' size 10m;

Create tablespace par08 datafile ' +dgdatga ' size 10m;

Create tablespace par09 datafile ' +dgdatga ' size 10m;

Create tablespace par010 datafile ' +dgdatga ' size 10m;

Create tablespace par011 datafile ' +dgdatga ' size 10m;

Create tablespace par012 datafile ' +dgdatga ' size 10m;

Alter user Fmismain quota unlimited on Par01;

Alter user Fmismain quota unlimited on par02;

Alter user Fmismain quota unlimited on par03;

Alter user Fmismain quota unlimited on par04;

Alter user Fmismain quota unlimited on par05;

Alter user Fmismain quota unlimited on par06;

Alter user Fmismain quota unlimited on par07;

Alter user Fmismain quota unlimited on par08;

Alter user Fmismain quota unlimited on par09;

Alter user Fmismain quota unlimited on par010;

Alter user Fmismain quota unlimited on par011;

Alter user Fmismain quota unlimited on par012;

CREATE TABLE SALES12

(

SALES_ID number,

Sales_dt Date

)

Partition by range (SALES_DT)

Interval (numtoyminterval (1, ' MONTH '))

Store in (par01,par02,par03,par04,par05,par06,par07,par08,par09,par010,par011,par012)

(

Partition p0701 values less than (to_date (' 2007-02-01 ', ' yyyy-mm-dd '))

);

INSERT into SALES12 values (1, ' 01-jun-07 ');

Select Partition_name, high_value from user_tab_partitions where table_name = ' SALES12 ';

INSERT into SALES12 values (1, ' 01-may-07 ');

Select Table_name,partition_name,tablespace_name,high_value from user_tab_partitions where table_name = ' SALES12 ';

Sql> Select Table_name,partition_name,tablespace_name,high_value from user_tab_partitions where table_name = ' SALES12 ';

TABLE_NAME Partition_name tablespace_name High_value

-------------------- -------------------- ------------------------------ ----------------------------------------

SALES12 P0701 fmismain to_date (' 2007-02-01 00:00:00 ', ' syyyy-m

M-dd HH24:MI:SS ', ' Nls_calendar=gregoria

SALES12 sys_p41 PAR05 to_date (' 2007-06-01 00:00:00 ', ' syyyy-m

M-dd HH24:MI:SS ', ' Nls_calendar=gregoria

SALES12 sys_p25 PAR06 to_date (' 2007-07-01 00:00:00 ', ' syyyy-m