Oracle split partition and table compression

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); 

 

  

posted @ 2023-02-14 10:14  Ayumie  阅读(27)  评论(0编辑  收藏  举报