按季度、按周、按年

3.自动增加分区（11G）

----11g新特性，numtodsinterval/numtoyminterval

3.1 按月自动添加分区 (按季度)

----创建分区（每3个月添加一个分区）

create table t_mon

( seq  number not null,

  update_time  date,

  constraint t_mon_seq primary key(seq)

  )

  partition by range(update_time)

  interval(numtoyminterval(3,'month'))

  ( partition t_mon1 values less than(to_date('2016-01-01','yyyy-mm-dd')) tablespace users);

 

----查看T_MON表的分区情况

set linesize 999

set pagesize 999

col TABLE_NAME for a20

col PARTITION_NAME for a20

col HIGH_VALUE for a10

col TABLESPACE_NAME for a10

 

select  TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_MON';

TABLE_NAME    PARTITION_NAME       HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE

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

T_MON                T_MON1               TO_DATE('                 83 USERS

                                          2016-01-01

 

SQL> insert into t_mon values(1,sysdate);

1 row created.

 

SQL> select * from t_mon;

       SEQ UPDATE_TIME

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

         1 2016-07-19 20:49:56

 

select  TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_MON';

TABLE_NAME    PARTITION_NAME       HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE

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

T_MON                SYS_P43              TO_DATE('                 83 SYSTEM

                                          2016-10-01

T_MON                T_MON1               TO_DATE('                 83 USERS

                                          2016-01-01

 

----批量添加数据，查看分区表添加情况

/*

declare

 a date;

begin

 for i in 2..20 loop

  select sysdate+i into a from dual;

 dbms_output.put_line(a);

 end loop;

end;

/

declare

 a date;

begin

 for i in 2..20 loop

  select add_months(sysdate,i)  into a from dual;

 dbms_output.put_line(a);

 end loop;

end;

/

*/

 

begin

 for i in 2..14 loop

  insert into t_mon values(i,add_months(sysdate,i));

 end loop;

commit;

end;

/

 

SQL> select * from t_mon;

       SEQ UPDATE_TIME

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

         1 2016-07-19 20:55:39

         2 2016-09-19 22:00:00

         3 2016-10-19 22:00:00

         4 2016-11-19 22:00:00

         5 2016-12-19 22:00:00

         6 2017-01-19 22:00:00

         7 2017-02-19 22:00:00

         8 2017-03-19 22:00:00

         9 2017-04-19 22:00:00

        10 2017-05-19 22:00:00

        11 2017-06-19 22:00:00

        12 2017-07-19 22:00:00

        13 2017-08-19 22:00:00

        14 2017-09-19 22:00:00

14 rows selected.

 

 

----再次查看表分区情况

select  TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_MON';

 

TABLE_NAME     PARTITION_NAME       HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE

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

T_MON                SYS_P43              TO_DATE('                 83 SYSTEM

                                          2016-10-01

T_MON                SYS_P61              TO_DATE('                 83 SYSTEM

                                          2017-01-01

T_MON                SYS_P62              TO_DATE('                 83 SYSTEM

                                          2017-04-01

T_MON                SYS_P63              TO_DATE('                 83 SYSTEM

                                          2017-07-01

T_MON                SYS_P64              TO_DATE('                 83 SYSTEM

                                          2017-10-01

T_MON                T_MON1               TO_DATE('                 83 USERS

                                          2016-01-01

6 rows selected.

----现在有6个分区咯

SQL> select * from t_mon partition(sys_p61);

       SEQ UPDATE_TIME

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

         3 2016-10-19 22:00:00

         4 2016-11-19 22:00:00

         5 2016-12-19 22:00:00

 

 

----索引状态

select index_name,index_type,tablespace_name,table_type,status from user_indexes where table_name='T_MON';

INDEX_NAME       INDEX_TYPE                  TABLESPACE TABLE_TYPE  STATUS

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

T_MON_SEQ         NORMAL                      SYSTEM     TABLE       VALID

 

 

3.2 按天自动添加分区 (按周)

----创建分区（每7天添加一个分区） 

create table t_day

( seq  number not null,

  update_time  date,

  constraint t_day_seq primary key(seq)

  )

  partition by range(update_time)

  interval(numtodsinterval(7,'day'))

  ( partition t_day1 values less than(to_date('2016-08-15','yyyy-mm-dd')) tablespace users);

 

 

----查看T_DAY表的分区情况

set linesize 999

set pagesize 999

col TABLE_NAME for a20

col PARTITION_NAME for a20

col HIGH_VALUE for a10

col TABLESPACE_NAME for a10

 

select  TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_DAY';

 

TABLE_NAME      PARTITION_NAME       HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE

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

T_DAY                T_DAY1               TO_DATE('                 83 USERS

                                          2016-08-15

 

----批量添加数据，查看分区表添加情况

begin

 for i in 1..7 loop

  insert into t_day values(i, sysdate+i*7);

 end loop;

commit;

end;

/

 

SQL> select * from t_day;

       SEQ UPDATE_TIME

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

         1 2016-07-26 22:16:15

         2 2016-08-02 22:16:15

         3 2016-08-09 22:16:15

         4 2016-08-16 22:16:15

         5 2016-08-23 22:16:15

         6 2016-08-30 22:16:15

         7 2016-09-06 22:16:15

7 rows selected.

 

----再次查看表分区情况

select  TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_DAY';

TABLE_NAME           PARTITION_NAME       HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE

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

T_DAY                SYS_P83              TO_DATE('                 83 SYSTEM

                                          2016-08-22

T_DAY                SYS_P84              TO_DATE('                 83 SYSTEM

                                          2016-08-29

T_DAY                SYS_P85              TO_DATE('                 83 SYSTEM

                                          2016-09-05

T_DAY                SYS_P86              TO_DATE('                 83 SYSTEM

                                          2016-09-12

T_DAY                T_DAY1               TO_DATE('                 83 USERS

                                          2016-08-15

 

SQL> select * from t_day partition(sys_p83);

       SEQ UPDATE_TIME

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

         4 2016-08-16 22:16:15

 

select index_name,index_type,tablespace_name,table_type,status from user_indexes where table_name='T_DAY';

INDEX_NAME         INDEX_TYPE                  TABLESPACE TABLE_TYPE  STATUS

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

T_DAY_SEQ         NORMAL                      SYSTEM     TABLE       VALID

 

 

3.3 按年自动添加分区 (按年)

----创建分区（每1年添加一个分区）

create table t_year

( seq  number not null,

  update_time  date,

  constraint t_year_seq primary key(seq)

  )

  partition by range(update_time)

  interval(numtoyminterval(1,'year'))

  ( partition t_year1 values less than(to_date('2016-01-01','yyyy-mm-dd')) tablespace users);

 

----查看T_YEAR表的分区情况

set linesize 999

set pagesize 999

col TABLE_NAME for a20

col PARTITION_NAME for a20

col HIGH_VALUE for a10

col TABLESPACE_NAME for a10

 

select  TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_YEAR';

TABLE_NAME    PARTITION_NAME       HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE

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

T_YEAR               T_YEAR1              TO_DATE('                 83 USERS

                                          2016-01-01

 

----批量添加数据，查看分区表添加情况

SQL> select sysdate,sysdate+numtoyminterval(1,'year') from dual;

SYSDATE             SYSDATE+NUMTOYMINTE

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

2016-07-19 22:33:27  2017-07-19 22:33:27

 

 

begin

 for i in 1..5 loop

  insert into t_year values(i,sysdate+numtoyminterval(i,'year'));

 end loop;

commit;

end;

/

 

 

SQL> select * from t_year;

       SEQ UPDATE_TIME

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

         1 2017-07-19 22:35:00

         2 2018-07-19 22:35:00

         3 2019-07-19 22:35:00

         4 2020-07-19 22:35:00

         5 2021-07-19 22:35:01

 

 

----再次查看表分区情况

select  TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_YEAR';

TABLE_NAME   PARTITION_NAME       HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE

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

T_YEAR               SYS_P87              TO_DATE('                 83 SYSTEM

                                          2018-01-01

T_YEAR               SYS_P88              TO_DATE('                 83 SYSTEM

                                          2019-01-01

T_YEAR               SYS_P89              TO_DATE('                 83 SYSTEM

                                          2020-01-01

T_YEAR               SYS_P90              TO_DATE('                 83 SYSTEM

                                          2021-01-01

T_YEAR               SYS_P91              TO_DATE('                 83 SYSTEM

                                          2022-01-01

T_YEAR               T_YEAR1              TO_DATE('                 83 USERS

                                          2016-01-01

6 rows selected.

 

----现在有6个分区咯

SQL> select * from t_year partition(sys_p91);

       SEQ UPDATE_TIME

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

         5 2021-07-19 22:35:01

 

----索引状态

select index_name,index_type,tablespace_name,table_type,status from user_indexes where table_name='T_YEAR';

INDEX_NAME          INDEX_TYPE                  TABLESPACE TABLE_TYPE  STATUS

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

T_YEAR_SEQ           NORMAL                      SYSTEM     TABLE       VALID