Oracle技术之11g分区表按时间自动创建-CSDN博客

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

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

oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html

转载于:https://blog.51cto.com/19880614/1255002