AAA oracle11g Automatically create partitions __oracle

Oracle's partitioned tables can include multiple partitions, each of which is a separate segment (SEGMENT) that can be stored in a different table space. Queries can be queried by querying tables to access the data in each partition, or by specifying the partition directly at the time of the query.

Before 11g, maintenance partitions need to be manual. 11g after the use of interval to implement automatic expansion of partitions, simplifying maintenance. According to Year: INTERVAL (Numtoyminterval (1, ' years '))


According to month: INTERVAL (numtoyminterval (1, ' MONTH '))
According to Days: INTERVAL (Numtodsinterval (1, ' Day '))


According to minutes and seconds: Numtodsinterval (n, {' Day ' | ') HOUR ' | ' MINUTE ' | ' SECOND '})


The following is a monthly automatic expansion to do a test:


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 partitions at the beginning of Sys are automatically expanded
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 then look at the T_range table structure, which can be viewed by plsql, with multiple columns of partition information


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);


--If you don't feel good about the partition name, 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;