时间范围分区自动新建
前段时间做了个分区功能，表结构如下：
-- Create table create table SUM_DUTY ( oid VARCHAR2(36) default sys_guid() not null, entry_id VARCHAR2(18) not null, manual_no VARCHAR2(12), p_date DATE, i_e_flag VARCHAR2(1), traf_mode VARCHAR2(1), trade_code VARCHAR2(10), trade_name VARCHAR2(255), trade_mode VARCHAR2(4), cut_mode VARCHAR2(3), tax_type VARCHAR2(1), real_tax NUMBER(19,2), center_code VARCHAR2(4), center_name VARCHAR2(255), scene_code VARCHAR2(4), scene_name VARCHAR2(255), manual_type VARCHAR2(4), inserttime DATE default SYSDATE ) partition by range (P_DATE) subpartition by list (MANUAL_TYPE) ( partition SUM_DUTY_2014_01 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ( subpartition SUM_DUTY_2014_01_MANUAL_B values ('B23'), subpartition SUM_DUTY_2014_01_MANUAL_C values ('C23'), subpartition SUM_DUTY_2014_01_MANUAL_E values ('E23'), subpartition SUM_DUTY_2014_01_MANUAL_H values ('H23'), subpartition SUM_DUTY_2014_01_MANUAL_L values ('L23'), subpartition SUM_DUTY_2014_01_MANUAL_K values ('K23'), subpartition SUM_DUTY_2014_01_MANUAL_F values (DEFAULT) ), partition SUM_DUTY_2014_02 values less than (TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ( subpartition SUM_DUTY_2014_02_MANUAL_B values ('B23'), subpartition SUM_DUTY_2014_02_MANUAL_C values ('C23'), subpartition SUM_DUTY_2014_02_MANUAL_E values ('E23'), subpartition SUM_DUTY_2014_02_MANUAL_H values ('H23'), subpartition SUM_DUTY_2014_02_MANUAL_L values ('L23'), subpartition SUM_DUTY_2014_02_MANUAL_K values ('K23'), subpartition SUM_DUTY_2014_02_MANUAL_F values (DEFAULT) ), partition SUM_DUTY_2014_03 values less than (TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ( subpartition SUM_DUTY_2014_03_MANUAL_B values ('B23'), subpartition SUM_DUTY_2014_03_MANUAL_C values ('C23'), subpartition SUM_DUTY_2014_03_MANUAL_E values ('E23'), subpartition SUM_DUTY_2014_03_MANUAL_H values ('H23'), subpartition SUM_DUTY_2014_03_MANUAL_L values ('L23'), subpartition SUM_DUTY_2014_03_MANUAL_K values ('K23'), subpartition SUM_DUTY_2014_03_MANUAL_F values (DEFAULT) ), partition SUM_DUTY_2014_04 values less than (TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ( subpartition SUM_DUTY_2014_04_MANUAL_B values ('B23'), subpartition SUM_DUTY_2014_04_MANUAL_C values ('C23'), subpartition SUM_DUTY_2014_04_MANUAL_E values ('E23'), subpartition SUM_DUTY_2014_04_MANUAL_H values ('H23'), subpartition SUM_DUTY_2014_04_MANUAL_L values ('L23'), subpartition SUM_DUTY_2014_04_MANUAL_K values ('K23'), subpartition SUM_DUTY_2014_04_MANUAL_F values (DEFAULT) ), partition SUM_DUTY_2014_05 values less than (TO_DATE(' 2014-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ( subpartition SUM_DUTY_2014_05_MANUAL_B values ('B23'), subpartition SUM_DUTY_2014_05_MANUAL_C values ('C23'), subpartition SUM_DUTY_2014_05_MANUAL_E values ('E23'), subpartition SUM_DUTY_2014_05_MANUAL_H values ('H23'), subpartition SUM_DUTY_2014_05_MANUAL_L values ('L23'), subpartition SUM_DUTY_2014_05_MANUAL_K values ('K23'), subpartition SUM_DUTY_2014_05_MANUAL_F values (DEFAULT) ), partition SUM_DUTY_2014_06 values less than (TO_DATE(' 2014-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ( subpartition SUM_DUTY_2014_06_MANUAL_B values ('B23'), subpartition SUM_DUTY_2014_06_MANUAL_C values ('C23'), subpartition SUM_DUTY_2014_06_MANUAL_E values ('E23'), subpartition SUM_DUTY_2014_06_MANUAL_H values ('H23'), subpartition SUM_DUTY_2014_06_MANUAL_L values ('L23'), subpartition SUM_DUTY_2014_06_MANUAL_K values ('K23'), subpartition SUM_DUTY_2014_06_MANUAL_F values (DEFAULT) ), partition SUM_DUTY_2014_07 values less than (TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ( subpartition SUM_DUTY_2014_07_MANUAL_B values ('B23'), subpartition SUM_DUTY_2014_07_MANUAL_C values ('C23'), subpartition SUM_DUTY_2014_07_MANUAL_E values ('E23'), subpartition SUM_DUTY_2014_07_MANUAL_H values ('H23'), subpartition SUM_DUTY_2014_07_MANUAL_L values ('L23'), subpartition SUM_DUTY_2014_07_MANUAL_K values ('K23'), subpartition SUM_DUTY_2014_07_MANUAL_F values (DEFAULT) ), partition SUM_DUTY_2014_08 values less than (TO_DATE(' 2014-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ( subpartition SUM_DUTY_2014_08_MANUAL_B values ('B23'), subpartition SUM_DUTY_2014_08_MANUAL_C values ('C23'), subpartition SUM_DUTY_2014_08_MANUAL_E values ('E23'), subpartition SUM_DUTY_2014_08_MANUAL_H values ('H23'), subpartition SUM_DUTY_2014_08_MANUAL_L values ('L23'), subpartition SUM_DUTY_2014_08_MANUAL_K values ('K23'), subpartition SUM_DUTY_2014_08_MANUAL_F values (DEFAULT) ), partition SUM_DUTY_2014_09 values less than (TO_DATE(' 2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ( subpartition SUM_DUTY_2014_09_MANUAL_B values ('B23'), subpartition SUM_DUTY_2014_09_MANUAL_C values ('C23'), subpartition SUM_DUTY_2014_09_MANUAL_E values ('E23'), subpartition SUM_DUTY_2014_09_MANUAL_H values ('H23'), subpartition SUM_DUTY_2014_09_MANUAL_L values ('L23'), subpartition SUM_DUTY_2014_09_MANUAL_K values ('K23'), subpartition SUM_DUTY_2014_09_MANUAL_F values (DEFAULT) ), partition SUM_DUTY_2014_10 values less than (TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ( subpartition SUM_DUTY_2014_10_MANUAL_B values ('B23'), subpartition SUM_DUTY_2014_10_MANUAL_C values ('C23'), subpartition SUM_DUTY_2014_10_MANUAL_E values ('E23'), subpartition SUM_DUTY_2014_10_MANUAL_H values ('H23'), subpartition SUM_DUTY_2014_10_MANUAL_L values ('L23'), subpartition SUM_DUTY_2014_10_MANUAL_K values ('K23'), subpartition SUM_DUTY_2014_10_MANUAL_F values (DEFAULT) ), partition SUM_DUTY_2014_11 values less than (TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ( subpartition SUM_DUTY_2014_11_MANUAL_B values ('B23'), subpartition SUM_DUTY_2014_11_MANUAL_C values ('C23'), subpartition SUM_DUTY_2014_11_MANUAL_E values ('E23'), subpartition SUM_DUTY_2014_11_MANUAL_H values ('H23'), subpartition SUM_DUTY_2014_11_MANUAL_L values ('L23'), subpartition SUM_DUTY_2014_11_MANUAL_K values ('K23'), subpartition SUM_DUTY_2014_11_MANUAL_F values (DEFAULT) ), partition SUM_DUTY_2014_12 values less than (TO_DATE(' 2014-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ( subpartition SUM_DUTY_2014_12_MANUAL_B values ('B23'), subpartition SUM_DUTY_2014_12_MANUAL_C values ('C23'), subpartition SUM_DUTY_2014_12_MANUAL_E values ('E23'), subpartition SUM_DUTY_2014_12_MANUAL_H values ('H23'), subpartition SUM_DUTY_2014_12_MANUAL_L values ('L23'), subpartition SUM_DUTY_2014_12_MANUAL_K values ('K23'), subpartition SUM_DUTY_2014_12_MANUAL_F values (DEFAULT) ) );
后要求以后当年自动新建下一年分区：
create or replace procedure SP_AutoCreatePartition is /*自动新建系统中所有分区表的下一年的分区*/ /*过程中涉及DBA_TAB_PARTITIONS的访问，必须给用当前用户select权限：grant select on DBA_TAB_PARTITIONS to 用户*/ V_COUNT NUMBER; --用于判断是否已存在分区 V_USERNAME VARCHAR2(100); --当前用户 V_DATE_STR VARCHAR2(100); --下一年年份 X NUMBER; --一年十二个月 V_PARTITION_DATE VARCHAR2(100);--分区的日期格式 V_DATA_STRING VARCHAR2(100); --分区值年月 V_SQL VARCHAR2(4000); begin SELECT USER INTO V_USERNAME FROM DUAL;--获取当前用户 SELECT TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE,12),'YYYY'),'YYYY') INTO V_DATE_STR FROM DUAL;--获取下一年的年份 SELECT COUNT(1) INTO V_COUNT FROM DBA_TAB_PARTITIONS T WHERE T.TABLE_NAME='SUM_DUTY' AND INSTR(T.PARTITION_NAME,V_DATE_STR)>0 AND T.TABLE_OWNER=V_USERNAME;--计算当前表中是否已存在下一年的分区 IF V_COUNT=0 THEN x := 1; WHILE x <=12 LOOP SELECT TO_CHAR(ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE,12),'YYYY'),x-1),'YYYY_MM') INTO V_PARTITION_DATE FROM DUAL; SELECT TO_CHAR(ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE,12),'YYYY'),x-1),'YYYY-MM') INTO V_DATA_STRING FROM DUAL; V_SQL:='ALTER TABLE SUM_DUTY ADD partition SUM_DUTY_'||V_PARTITION_DATE||' values less than (TO_DATE('''||V_DATA_STRING||'-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) ( subpartition SUM_DUTY_'||V_PARTITION_DATE||'_MANUAL_B values (''B23''), subpartition SUM_DUTY_'||V_PARTITION_DATE||'_MANUAL_C values (''C23''), subpartition SUM_DUTY_'||V_PARTITION_DATE||'_MANUAL_E values (''E23''), subpartition SUM_DUTY_'||V_PARTITION_DATE||'_MANUAL_H values (''H23''), subpartition SUM_DUTY_'||V_PARTITION_DATE||'_MANUAL_L values (''L23''), subpartition SUM_DUTY_'||V_PARTITION_DATE||'_MANUAL_K values (''K23''), subpartition SUM_DUTY_'||V_PARTITION_DATE||'_MANUAL_F values (DEFAULT) )'; EXECUTE IMMEDIATE V_SQL; x := x + 1; END LOOP; END IF; end SP_AutoCreatePartition;
