分区表 自动添加

背景：

数据较多，切考虑到后期维护，希望能够自动添加分区

过程 

间隔分区 oracle 11g 新的特性

CREATE TABLE gh_interval_tab (

  id           NUMBER,

  created_date DATE

)

PARTITION BY RANGE (created_date)

INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

(

   PARTITION part_01 values LESS THAN (TO_DATE('2016-01-01','yyyy-mm-dd'))

);

insert into gh_interval_tab(id,created_date) values(1000,to_date('2015-01-01','yyyy-mm-dd'));

insert into gh_interval_tab(id,created_date) values(1000,to_date('2015-02-01','yyyy-mm-dd'));

insert into gh_interval_tab(id,created_date) values(1000,to_date('2015-03-01','yyyy-mm-dd'));

insert into gh_interval_tab(id,created_date) values(1000,to_date('2015-04-01','yyyy-mm-dd'));

insert into gh_interval_tab(id,created_date) values(1000,to_date('2015-05-01','yyyy-mm-dd'));

commit;

SQL> select t.segment_name,t.partition_name,t.segment_name,t.tablespace_name from user_segments t;

SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_NAME                                                                     TABLESPACE_NAME

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

GH_INTERVAL_TAB                                                                  PART_01                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA

SQL> select s.* from user_tab_partitions s;

TABLE_NAME                     COMPOSITE PARTITION_NAME                 SUBPARTITION_COUNT HIGH_VALUE                                                                       HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME                  PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT   MAX_SIZE PCT_INCREASE  FREELISTS FREELIST_GROUPS LOGGING COMPRESSION COMPRESS_FOR   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE GLOBAL_STATS USER_STATS IS_NESTED PARENT_TABLE_PARTITION         INTERVAL SEGMENT_CREATED

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

GH_INTERVAL_TAB                NO        PART_01                                         0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  1 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       NO       YES

insert into gh_interval_tab(id,created_date) values(1000,to_date('2016-01-01','yyyy-mm-dd'));

insert into gh_interval_tab(id,created_date) values(1000,to_date('2016-02-01','yyyy-mm-dd'));

insert into gh_interval_tab(id,created_date) values(1000,to_date('2016-03-01','yyyy-mm-dd'));

insert into gh_interval_tab(id,created_date) values(1000,to_date('2016-04-01','yyyy-mm-dd'));

insert into gh_interval_tab(id,created_date) values(1000,to_date('2016-05-01','yyyy-mm-dd'));

GH_INTERVAL_TAB                NO        SYS_P64                                         0 TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  2 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       YES      YES

GH_INTERVAL_TAB                NO        SYS_P65                                         0 TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  3 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       YES      YES

GH_INTERVAL_TAB                NO        SYS_P66                                         0 TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  4 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       YES      YES

GH_INTERVAL_TAB                NO        SYS_P67                                         0 TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  5 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       YES      YES

GH_INTERVAL_TAB                NO        SYS_P68                                         0 TO_DATE(' 2016-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  6 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       YES      YES

6 rows selected

过程中2015的数据插入了5条，都存储在oracle

CREATE TABLE gh_interval_tab_num (

PARTITION BY RANGE (id)

INTERVAL (10000)

   partition p_1w values less than (10001)

GH_INTERVAL_TAB_NUM            NO        P_1W                                            0 10001                                                                                            5                  1 TS_ZYK_DATA                            10                     1        255                                                                                                     YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       NO       NO

7 rows selected

GH_INTERVAL_TAB                                                                  SYS_P64                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA

GH_INTERVAL_TAB                                                                  SYS_P65                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA

GH_INTERVAL_TAB                                                                  SYS_P66                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA

GH_INTERVAL_TAB                                                                  SYS_P67                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA

GH_INTERVAL_TAB                                                                  SYS_P68                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA

可以发现一个情况在user_segment 中数据只有出现数据才会存在，这是oracle11g新的特性

insert into gh_interval_tab_num(id,created_date) values(1,to_date('2016-01-01','yyyy-mm-dd'));

GH_INTERVAL_TAB_NUM                                                              P_1W                           GH_INTERVAL_TAB_NUM        

出现了GH_INTERVAL_TAB_NUM信息

具体步骤：

部分资料摘自官网文档：

<a href="https://oracle-base.com/articles/11g/partitioning-enhancements-11gr1">https://oracle-base.com/articles/11g/partitioning-enhancements-11gr1</a>