背景：
数据较多，切考虑到后期维护，希望能够自动添加分区
过程
间隔分区 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'));
commit;
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
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 (
id NUMBER,
created_date DATE
)
PARTITION BY RANGE (id)
INTERVAL (10000)
(
partition p_1w values less than (10001)
)
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
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
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
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
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
6 rows selected
可以发现一个情况在user_segment 中数据只有出现数据才会存在，这是oracle11g新的特性
insert into gh_interval_tab_num(id,created_date) values(1,to_date('2016-01-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
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
GH_INTERVAL_TAB_NUM P_1W GH_INTERVAL_TAB_NUM
出现了GH_INTERVAL_TAB_NUM信息
具体步骤：
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'))
);
CREATE TABLE gh_interval_tab_num (
id NUMBER,
created_date DATE
)
PARTITION BY RANGE (id)
INTERVAL (10000)
(
partition p_1w values less than (10001)
)
部分资料摘自官网文档：