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