在生产库中经常要用到分区表，而且很多是按天或月来分区的，在10g之前，需要提前添加好分区，比如按月分区，到8月份低就要手动添加9月份的分区P201609，如果不添加那么数据会插入报错或者插入到MAXVALUE对应的分区，数据管理混乱。在Oracle11g中解决了这种繁琐的手动添加，变成Oracle自动按日/月扩展，非常方便。
一、日分区表测试
1、创建日分区表
SQL> create table test1(id int,name varchar2(20),deal_time date default sysdate) partition by range(deal_time) INTERVAL(numtodsinterval(1,'DAY'))  ( PARTITION part_01 values LESS THAN (TO_DATE('20160905','YYYYMMDD')) );

Table created.
2、插入数据
SQL> insert into test1 select rownum,'lyn'||rownum name,sysdate-4 from dual connect by level<=1000;

1000 rows created.

Elapsed: 00:00:00.01

SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

SQL> insert into test1 select rownum,'sky'||rownum name,sysdate-3 from dual connect by level<=1000;

1000 rows created

.Elapsed: 00:00:00.02

SQL> insert into test1 select rownum,'moon'||rownum name,sysdate-2 from dual connect by level<=1000;

1000 rows created.

Elapsed: 00:00:00.02

SQL> insert into test1 select rownum,'fly'||rownum name,sysdate-1 from dual connect by level<=1000;

1000 rows created.

Elapsed: 00:00:00.02

SQL> insert into test1 select rownum,'ted'||rownum name,sysdate from dual connect by level<=1000;

1000 rows created.

Elapsed: 00:00:00.02

SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

SQL> select count(*) from test1;

COUNT(*)----------    

5000

Elapsed: 00:00:00.00
3、查看分区信息
SQL> select owner,segment_name,partition_name,bytes size_b from dba_segments where owner='YSWORK_LXW' and segment_name='TEST1';

OWNER                          SEGMENT_NAME                   PARTITION_NAME       SIZE_B
------------------------------ ------------------------------ ------------------------------ ----------
YSWORK_LXW                     TEST1                          PART_01                           8388608
YSWORK_LXW                     TEST1                          SYS_P41                           8388608
YSWORK_LXW                     TEST1                          SYS_P42                           8388608
YSWORK_LXW                     TEST1                          SYS_P43                           8388608
YSWORK_LXW                     TEST1                          SYS_P44                           8388608

Elapsed: 00:00:01.57
SQL> select to_char(deal_time,'yyyy-mm-dd') day,count(*) from test1 group by to_char(deal_time,'yyyy-mm-dd');

DAY          COUNT(*)
---------- ----------
2016-09-04       1000
2016-09-08       1000
2016-09-06       1000
2016-09-07       1000
2016-09-05       1000

Elapsed: 00:00:00.01
继续插入数据
SQL> insert into test1 select rownum,'lyn'||rownum name,sysdate-5 from dual connect by level<=1000;

1000 rows created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select to_char(deal_time,'yyyy-mm-dd') day,count(*) from test1 group by to_char(deal_time,'yyyy-mm-dd');

DAY          COUNT(*)
---------- ----------
2016-09-04       1000
2016-09-08       1000
2016-09-06       1000
2016-09-03       1000
2016-09-07       1000
2016-09-05       1000

6 rows selected.

Elapsed: 00:00:00.00

SQL> select owner,segment_name,partition_name,bytes size_b from dba_segments where owner='YSWORK_LXW' and segment_name='TEST1';

OWNER                          SEGMENT_NAME                   PARTITION_NAME       SIZE_B
------------------------------ ------------------------------ ------------------------------ ----------
YSWORK_LXW                     TEST1                          PART_01                           8388608
YSWORK_LXW                     TEST1                          SYS_P41                           8388608
YSWORK_LXW                     TEST1                          SYS_P42                           8388608
YSWORK_LXW                     TEST1                          SYS_P43                           8388608
YSWORK_LXW                     TEST1                          SYS_P44                           8388608

Elapsed: 00:00:01.57
插入了6天的数据，结果还是5个分区，因为是我们的定义如下：
PARTITION part_01 values LESS THAN (TO_DATE('20160905','YYYYMMDD')
即小于20160905的deal_time的数据都会插入到Part_01，在它之后deal_time数据oracle会自动创建分区并插入时间。
4、总结
按日分区的表，必须指定起始分区，小于起始分区指定的时间的数据插入到起始分区，不创建新分区，在起始分区之后时间数据，oracle会自动添加新的日分区，并将数据插入其中。
二、月分区测试
1、创建月分区表
SQL> create table test2(id int,name varchar2(20),deal_time date default sysdate) partition by range(deal_time) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
  2    (PARTITION P1 VALUES LESS THAN (TO_DATE('2016-8-1', 'YYYY-MM-DD')));

Table created.
2、插入数据
SQL> insert into test2 select rownum,'lyn'||rownum name,sysdate-31 from dual connect by level<=1000;

1000 rows created.

Elapsed: 00:00:00.02
SQL> insert into test2 select rownum,'sky'||rownum name,sysdate-31*2 from dual connect by level<=1000;

1000 rows created.

Elapsed: 00:00:00.00
SQL> insert into test2 select rownum,'moon'||rownum name,sysdate-31*3 from dual connect by level<=1000;

1000 rows created.

Elapsed: 00:00:00.00
SQL> insert into test2 select rownum,'ted'||rownum name,sysdate from dual connect by level<=1000;

1000 rows created.

Elapsed: 00:00:00.02
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
3、查看Segment信息
SQL> select owner,segment_name,partition_name,bytes size_b,tablespace_name from dba_segments where owner='YSWORK_LXW' and segment_name='TEST2';

OWNER                          SEGMENT_NAME                   PARTITION_NAME                     SIZE_B TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ----------
YSWORK_LXW                     TEST2                          P1                                8388608 LS6_ACCT_DATA
YSWORK_LXW                     TEST2                          SYS_P45                           8388608 LS6_ACCT_DATA
YSWORK_LXW                     TEST2                          SYS_P46                           8388608 LS6_ACCT_DATA

Elapsed: 00:00:01.44
SQL> select to_char(deal_time,'yyyy-mm') month,count(*) from test1 group by to_char(deal_time,'yyyy-mm');

MONTH     COUNT(*)
------- ----------
2016-08       1000
2016-07       1000
2016-09       4000
2016-06       1000

Elapsed: 00:00:00.01
SQL> select owner,segment_name,partition_name,bytes/1024/1024 size_mb,tablespace_name from dba_segments where owner='YSWORK_LXW' and segment_name='TEST2';

OWNER                          SEGMENT_NAME                   PARTITION_NAME                    SIZE_MB TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ----------
YSWORK_LXW                     TEST2                          P1                                      8 LS6_ACCT_DATA
YSWORK_LXW                     TEST2                          SYS_P45                                 8 LS6_ACCT_DATA
YSWORK_LXW                     TEST2                          SYS_P46                                 8 LS6_ACCT_DATA

Elapsed: 00:00:01.53
结论：虽然指定了起始插入时间，如果插入时间比该时间小就会自动插入P1分区，不会创建新的分区。
SQL> select owner,segment_name,partition_name,bytes size_b,tablespace_name from dba_segments where owner='YSWORK_LXW' and segment_name='TEST1';

OWNER                          SEGMENT_NAME                   PARTITION_NAME                     SIZE_B TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ----------
YSWORK_LXW                     TEST1                          PART_01                           8388608 LS6_ACCT_DATA
YSWORK_LXW                     TEST1                          SYS_P42                           8388608 LS6_ACCT_DATA
YSWORK_LXW                     TEST1                          SYS_P43                           8388608 LS6_ACCT_DATA
YSWORK_LXW                     TEST1                          SYS_P44                           8388608 LS6_ACCT_DATA

Elapsed: 00:00:01.50
三、分区操作
1、Oracle分区重命名
     Oracle自动添加的分区名SYS_P42没有意义，我们希望分区直观有意义，比如按月份的分区名P20108,现在是9月份，我要清除8月份数据，就可以直接drop partition P201608，如果是SYS_P42只能通过表结构的Less than去确定时间范围。
SQL> alter table test1 rename partition PART_01 to p20160904;

Table altered.

Elapsed: 00:00:00.02
SQL> alter table test1 rename partition SYS_P42 to p20160905;

Table altered.

Elapsed: 00:00:00.01
SQL> alter table test1 rename partition SYS_P43 to p20160906;

Table altered.

Elapsed: 00:00:00.01
SQL> alter table test1 rename partition SYS_P44 to p20160907;

Table altered.

Elapsed: 00:00:00.01
SQL> select owner,segment_name,partition_name,bytes size_b,tablespace_name from dba_segments where owner='YSWORK_LXW' and segment_name='TEST1';

OWNER                          SEGMENT_NAME                   PARTITION_NAME                     SIZE_B TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ----------
YSWORK_LXW                     TEST1                          P20160904                         8388608 LS6_ACCT_DATA
YSWORK_LXW                     TEST1                          P20160905                         8388608 LS6_ACCT_DATA
YSWORK_LXW                     TEST1                          P20160906                         8388608 LS6_ACCT_DATA
YSWORK_LXW                     TEST1                          P20160907                         8388608 LS6_ACCT_DATA

Elapsed: 00:00:01.58
2、删除分区
SQL> alter table test2 drop partition p201609;

Table altered.

Elapsed: 00:00:00.03
3、清除分区
SQL> alter table test2 truncate partition p201608;

Table altered.

Elapsed: 00:00:00.03
4、总结
删除分区全局索引失效
truncate分区是全局索引失效
exchange分区全局索引和交换所在的分区的索引失效
5、重建索引
1）全局索引
SQL> select 'alter index '||owner||'.'||index_name||' rebuild  online nologging  parallel 8;' from dba_indexes where status='UNUSABLE';

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDONLINENOLOGGINGPARALLEL8;'
----------------------------------------------------------------------------------------------------------------
alter index YSWORK_LXW.I1_TEST2 rebuild  online nologging  parallel 8;

Elapsed: 00:00:00.06
2）分区索引
SQL> select   'alter index '||t1.index_owner||'.'||t1.index_name||' rebuild partition '||partition_name||' online parallel 12;'from dba_ind_partitions t1,dba_indexes t2 where t1.index_name=t2.index_name and t1.status='UNUSABLE';

no rows selected

Elapsed: 00:00:00.43
Oracle11g的自动按日/月的分区扩展还是很方便，建议在生产中使用，尤其是那种LOG表比较适合使用。