Oracle add partition by MONTH (Compress)

--add partition by MONTH (Compress) 20230213 
--SELECT TABLE_NAME,PARTITION_NAME,COMPRESSION,T.* FROM USER_TAB_PARTITIONS T WHERE TABLE_NAME = 'TEST_COMPRESS'; 
DECLARE
  V_BEGIN_DT     DATE;
  IN_PART_TYPE   VARCHAR2(30):= 'PM_MAX';
  STR_PART_DT    VARCHAR2(30);
  STR_DT         VARCHAR2(30);
  V_SQL          VARCHAR2(2000);
  V_SQL1          VARCHAR2(2000);
BEGIN
  FOR I IN(SELECT TABLE_NAME,MAX(PARTITION_NAME) AS PARTITION_NAME
             FROM USER_TAB_PARTITIONS T 
            WHERE T.TABLE_NAME IN ('TEST_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*/V_SQL:= '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||',PARTITION '||IN_PART_TYPE||')';
            DBMS_OUTPUT.PUT_LINE('V_SQL:'||V_SQL); 
            
            V_SQL1:= 'ALTER TABLE '||I.TABLE_NAME||' MODIFY PARTITION PM_'||STR_DT||' COMPRESS';
            
            DBMS_OUTPUT.PUT_LINE('V_SQL1:'||V_SQL1);
            
            EXECUTE IMMEDIATE V_SQL;
            EXECUTE IMMEDIATE V_SQL1;
            
        V_BEGIN_DT := ADD_MONTHS(V_BEGIN_DT,1);
    END LOOP;
  END LOOP;
END;
/  

  

posted @ 2023-02-13 17:42  Ayumie  阅读(32)  评论(0编辑  收藏  举报