Oracle split partition and table compression

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

DECLARE   V_BEGIN_DT     DATE;   IN_PART_TYPE   VARCHAR2(30):= 'PM_MAX';   STR_PART_DT    VARCHAR2(30);   STR_DT         VARCHAR2(30); BEGIN   FOR I IN(SELECT TABLE_NAME,MAX(PARTITION_NAME) AS PARTITION_NAME              FROM USER_TAB_PARTITIONS T             WHERE T.TABLE_NAME IN ('TEST_SPLIT_COMPRESS')               AND PARTITION_NAME LIKE'PM%'               AND PARTITION_NAME NOT IN('PM_MAX')             GROUP BY TABLE_NAME)   LOOP    V_BEGIN_DT := ADD_MONTHS(TO_DATE(SUBSTR(I.PARTITION_NAME,4),'YYYYMM'),1);     WHILE V_BEGIN_DT <= DATE'2022-08-31' --扩展至20220831     LOOP       STR_PART_DT := TO_CHAR(ADD_MONTHS(V_BEGIN_DT,1), 'YYYY-MM-DD HH24:MI:SS');       STR_DT := TO_CHAR(V_BEGIN_DT,'YYYYMM');       EXECUTE IMMEDIATE 'ALTER TABLE '||I.TABLE_NAME||' SPLIT PARTITION '||IN_PART_TYPE             ||' AT(TO_DATE('''||STR_PART_DT||''',''YYYY-MM-DD HH24:MI:SS''))             INTO (PARTITION PM_'||STR_DT||' COMPRESS,PARTITION '||IN_PART_TYPE||')';                                     V_BEGIN_DT := ADD_MONTHS(V_BEGIN_DT,1);     END LOOP;   END LOOP; END; / ALTER TABLE TEST_SPLIT_COMPRESS SPLIT PARTITION PM_MAX AT(TO_DATE('2022-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))             INTO (PARTITION PM_202202 COMPRESS,PARTITION PM_MAX);