oracle 12c中自增分區(qū)表 自動調整緩存分區(qū)的存儲過程
最近為了使用12c的強大的inmemory功能，新做了一個OGG從11gR2 RAC到12c的數據同步，分擔主庫的olap業(yè)務
其中有幾張分區(qū)表，按月分區(qū)自增，只緩存半年的數據，這樣每個月的1號就需要緩存新一個月的分區(qū)進去，同時需要移除6個月前的分區(qū)，所以寫了個過程，做個簡單記錄
-
CREATE OR REPLACE PROCEDURE PARTITION_CACHE
-
IS
-
TYPE REF_CURSOR_TYPE IS REF CURSOR;
-
C1 REF_CURSOR_TYPE; --動態(tài)游標，用來循環(huán)處理需要緩存的5張表
-
V_HIGHVALUE DBA_TAB_PARTITIONS.HIGH_VALUE%TYPE; --自增分區(qū)因為分區(qū)名不確定，需要根據分區(qū)的最大值來獲取分區(qū)名
-
V_PARTNAME DBA_TAB_PARTITIONS.PARTITION_NAME%TYPE; --分區(qū)名
-
V_HIGHMONTH NVARCHAR2(10); --最大分區(qū)值截取成月份格式
-
V_CURRMONTH NVARCHAR2(10); --當前月
-
V_OLDMONTH NVARCHAR2(10); --6個月前的月份
-
V_SQL1 NVARCHAR2(1000); --移除舊分區(qū)的sql
-
V_SQL2 NVARCHAR2(1000); --緩存新分區(qū)的sql
-
V_SQL VARCHAR2(1000); --獲取分區(qū)名和分區(qū)最大值的sql
-
-
BEGIN
-
V_CURRMONTH := TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYYY-MM');
-
V_OLDMONTH := TO_CHAR(ADD_MONTHS(SYSDATE,-6),'YYYY-MM');
-
-
DECLARE CURSOR C2 IS SELECT * FROM DBA_TABLES WHERE TABLE_NAME IN ('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5') ORDER BY TABLE_NAME;
-
BEGIN
-
FOR TABNAME IN C2 --循環(huán)操作5張表
-
LOOP
-
V_SQL := 'SELECT HIGH_VALUE,PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = '''|| TABNAME.TABLE_NAME||''' ORDER BY PARTITION_POSITION DESC';
-
OPEN C1 FOR V_SQL;
-
FETCH C1 INTO V_HIGHVALUE,V_PARTNAME;
-
WHILE (C1%FOUND) LOOP
-
V_HIGHMONTH := SUBSTR(V_HIGHVALUE,12,7);
-
IF (V_HIGHMONTH = V_OLDMONTH) THEN
-
DBMS_OUTPUT.PUT_LINE('表名:'||TABNAME.TABLE_NAME||' 需移除分區(qū)名稱:'||V_PARTNAME||' 分區(qū)最大值:'||V_OLDMONTH);
-
V_SQL1 := 'ALTER TABLE CCPS.'||TABNAME.TABLE_NAME||' MODIFY PARTITION '||V_PARTNAME||' NO INMEMORY';
-
DBMS_OUTPUT.PUT_LINE('SQL: '||V_SQL1);
-
ELSIF (V_HIGHMONTH = V_CURRMONTH) THEN
-
DBMS_OUTPUT.PUT_LINE('表名:'||TABNAME.TABLE_NAME||' 需緩存分區(qū)名稱:'||V_PARTNAME||' 分區(qū)最大值:'||V_HIGHMONTH);
-
V_SQL2 := 'ALTER TABLE CCPS.'||TABNAME.TABLE_NAME||' MODIFY PARTITION '||V_PARTNAME||' INMEMORY PRIORITY CRITICAL';
-
DBMS_OUTPUT.PUT_LINE('SQL: '||V_SQL2);
-
END IF;
-
FETCH C1 INTO V_HIGHVALUE,V_PARTNAME;
-
END LOOP;
-
CLOSE C1;
-
DBMS_OUTPUT.PUT_LINE(' ');
-
DBMS_OUTPUT.PUT_LINE('*******************************************');
-
END LOOP;
-
END;
-
END;
這里只是測試打印了sql，具體到線上改成執(zhí)行就OK了
剛接觸PLSQL，游標什么的也剛接觸，只是自己做個簡單記錄
代碼運行結果示例：
SQL> exec partition_cache
表名:TABLE1 需緩存分區(qū)名稱:SYS_P1167 分區(qū)最大值:2018-07
SQL: ALTER TABLE CCPS.TABLE1 MODIFY PARTITION SYS_P1167 INMEMORY PRIORITY CRITICAL
表名:TABLE1 需移除分區(qū)名稱:SYS_P388 分區(qū)最大值:2017-12
SQL: ALTER TABLE CCPS.TABLE1 MODIFY PARTITION SYS_P388 NO INMEMORY
*******************************************
表名:TABLE2 需移除分區(qū)名稱:SYS_P588 分區(qū)最大值:2017-12
SQL: ALTER TABLE CCPS.TABLE2 MODIFY PARTITION SYS_P588 NO INMEMORY
*******************************************
表名:TABLE3 需緩存分區(qū)名稱:SYS_P1168 分區(qū)最大值:2018-07
SQL: ALTER TABLE CCPS.TABLE3 MODIFY PARTITION SYS_P1168 INMEMORY PRIORITY CRITICAL
表名:TABLE3 需移除分區(qū)名稱:SYS_P328 分區(qū)最大值:2017-12
SQL: ALTER TABLE CCPS.TABLE3 MODIFY PARTITION SYS_P328 NO INMEMORY
*******************************************
表名:TABLE4 需移除分區(qū)名稱:SYS_P368 分區(qū)最大值:2017-12
SQL: ALTER TABLE CCPS.TABLE4 MODIFY PARTITION SYS_P368 NO INMEMORY
*******************************************
表名:TABLE5 需緩存分區(qū)名稱:SYS_P1166 分區(qū)最大值:2018-07
SQL: ALTER TABLE CCPS.TABLE5 MODIFY PARTITION SYS_P1166 INMEMORY PRIORITY CRITICAL
表名:TABLE5 需移除分區(qū)名稱:SYS_P428 分區(qū)最大值:2017-12
SQL: ALTER TABLE CCPS.TABLE5 MODIFY PARTITION SYS_P428 NO INMEMORY
*******************************************
PL/SQL procedure successfully completed
