Oracle通过最小分区增加分区的SQL

以下是参照changjian的分区脚本创建的从最小分区split分区的脚本,供参考。

--split partition from min partition_name 
DECLARE
  V_BEGIN_DT     DATE;
  IN_PART_TYPE   VARCHAR2(30):= 'PD_MAX';
  STR_PART_DT    VARCHAR2(30);
  STR_DT         VARCHAR2(30);
  v_partitionName VARCHAR2(500);
  
BEGIN
  FOR I IN(SELECT TABLE_NAME,MIN(PARTITION_NAME) AS PARTITION_NAME
             FROM USER_TAB_PARTITIONS T
            INNER JOIN CITI_WORKFLOW_TASK_CONFIG T1
               ON T.TABLE_NAME = 'TEST_TABLE'
             WHERE PARTITION_NAME NOT IN('PD_MAX')
            GROUP BY TABLE_NAME)
  LOOP
    V_BEGIN_DT := TO_DATE(SUBSTR(I.PARTITION_NAME,4),'YYYYMMDD');
    v_partitionName:=i.partition_name;
    
    WHILE V_BEGIN_DT >= DATE'2018-01-31' --扩展至20171231
    LOOP
      STR_PART_DT := trim(TO_CHAR(trunc(V_BEGIN_DT,'mm'), 'YYYY-MM-DD HH24:MI:SS'));
      STR_DT := TO_CHAR(last_day(add_months(V_BEGIN_DT,-1)),'YYYYMMDD');
      
            ||' AT(TO_DATE('||''||STR_PART_DT||''',''YYYY-MM-DD HH24:MI:SS''))
            INTO (PARTITION PD_'||STR_DT||',PARTITION '||v_partitionName||')');
                
      EXECUTE IMMEDIATE 'ALTER TABLE '||I.TABLE_NAME||' SPLIT PARTITION '||v_partitionName
            ||' AT(TO_DATE('''||STR_PART_DT||''',''YYYY-MM-DD HH24:MI:SS''))
            INTO (PARTITION PD_'||STR_DT||',PARTITION '||v_partitionName||')';
        V_BEGIN_DT := ADD_MONTHS(V_BEGIN_DT,-1);
        V_PARTITIONNAME:='PD_'||STR_DT;
    END LOOP;
  END LOOP;
END;