Oracle 11g创建Interval分区表

1.
     interval使用之后，就不用手工增加分区，来适应数据的增长。
2.
     根据年月 INTERVAL(NUMTO YM INTERVAL(1,'YEAR'))
                       INTERVAL(NUMTO YM INTERVAL(1,'MONTH'))
     根据天     INTERVAL(NUMTO DS INTERVAL(1,'DAY'))

3.

  CREATE TABLE hr.int_par(
               id number primary key,
               join_date date )
           partition by range(join_date) interval (numtoyMinterval (1,'YEAR'))
            -- store in (par01,par02,par03,par04,par05,par06,par07,par08,par09,par010,par011,par012)
           (
            partition inte_par1 VALUES LESS THAN (to_date('2000-9-9','YYYY-MM-DD'))
           )

4.
       BEGIN
                 FOR i IN 1..10 LOOP
                    EXECUTE IMMEDIATE 'INSERT INTO hr.int_par VALUES (:1,:2)'
                    USING i, trunc(to_date('2000-9-9','YYYY-MM-DD'),'yyyy')+366*i   ;
              END LOOP;
              COMMIT;
       END;
       /

5.
TABLE_NAME  PARTITION_NAME  HIGH_VALUE                                                                       PARTITION_POSITION
------------------------------------------------------------------------------------------------------------ ------------------- --
  INT_PAR     SYS_P50         TO_DATE(' 2010-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                  11
  INT_PAR     SYS_P49         TO_DATE(' 2009-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                  10
  INT_PAR     SYS_P48         TO_DATE(' 2008-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                   9
  INT_PAR     SYS_P47         TO_DATE(' 2007-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                   8
  INT_PAR     SYS_P46         TO_DATE(' 2006-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                   7
  INT_PAR     SYS_P45         TO_DATE(' 2005-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                   6
  INT_PAR     SYS_P44         TO_DATE(' 2004-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                   5
  INT_PAR     SYS_P43         TO_DATE(' 2003-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                   4
  INT_PAR     SYS_P42         TO_DATE(' 2002-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                   3
  INT_PAR     SYS_P41         TO_DATE(' 2001-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                   2
  INT_PAR     INTE_PAR1       TO_DATE(' 2000-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                   1

6.
        SQL> SELECT * FROM hr.int_par partition(sys_p46);

                         ID JOIN_DATE
                   ---------- -----------
                         6 2006-1-5

         SQL> SELECT * FROM hr.int_par partition(sys_p50);

                         ID JOIN_DATE
                  ---------- -----------
                         10 2010-1-8

7.