oracle按dt字段建立分区,Oracle技术之11g分区表按时间自动创建

11g新特性_分区表按时间自动创建(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

注意新分区 SYS_P42，其上限为 6 月 1 日，因此该分区可以保留 2006 年 5 月的数据。该分区是通过拆分 SYS_P41 分区创建的(针对 6 月份)。因此，当您定义一个间隔分区方案时，Oracle 会自动创建和维护分区。

如果您希望将分区存储在特定表空间中，可以使用 store in 子句执行该操作：

interval (numtoyminterval(1,'MONTH'))

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

该子句以循环方式将分区存储在表空间 TS1、TS2 和 TS3 中。

如：

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

M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

CREATE TABLE interval_tab (

id           NUMBER,

code         VARCHAR2(10),

description  VARCHAR2(50),

created_date DATE

)

PARTITION BY RANGE (created_date)

INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

(

PARTITION part_01 values LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))

);

EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

COLUMN table_name FORMAT A20

COLUMN partition_name FORMAT A20

COLUMN high_value FORMAT A40

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

INSERT INTO interval_tab VALUES (1, 'ONE', 'One', TO_DATE('16-OCT-2007', 'DD-MON-YYYY'));

INSERT INTO interval_tab VALUES (2, 'TWO', 'Two', TO_DATE('31-OCT-2007', 'DD-MON-YYYY'));

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

INSERT INTO interval_tab VALUES (3, 'THREE', 'Three', TO_DATE('01-NOV-2007', 'DD-MON-YYYY'));

INSERT INTO interval_tab VALUES (3, 'THREE', 'Three', TO_DATE('01-NOV-2007', 'DD-MON-YYYY'));

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

INSERT INTO interval_tab VALUES (5, 'FIVE', 'Five', TO_DATE('01-JAN-2008', 'DD-MON-YYYY'));

INSERT INTO interval_tab VALUES (4, 'FOUR', 'Four', TO_DATE('31-JAN-2008', 'DD-MON-YYYY'));

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

INSERT INTO interval_tab VALUES (5, 'FIVE', 'Five', TO_DATE('01-JAN-2008', 'DD-MON-YYYY'));

INSERT INTO interval_tab VALUES (4, 'FOUR', 'Four', TO_DATE('31-JAN-2008', 'DD-MON-YYYY'));

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS

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

INTERVAL_TAB         PART_01              TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M          2

M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

INTERVAL_TAB         SYS_P21              TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M          2

M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

INTERVAL_TAB         SYS_P22              TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M          5

M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA