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; /
All for u