oracle 12c中自增分区表自动调整缓存分区的存储过程

最近为了使用12c的强大的inmemory功能，新做了一个OGG从11gR2 RAC到12c的数据同步，分担主库的olap业务
其中有几张分区表，按月分区自增，只缓存半年的数据，这样每个月的1号就需要缓存新一个月的分区进去，同时需要移除6个月前的分区，所以写了个过程，做个简单记录

点击(此处)折叠或打开

CREATE OR REPLACE PROCEDURE PARTITION_CACHE
IS
TYPE REF_CURSOR_TYPE IS REF CURSOR;
C1 REF_CURSOR_TYPE; –动态游标，用来循环处理需要缓存的5张表
V_HIGHVALUE DBA_TAB_PARTITIONS.HIGH_VALUE%TYPE; –自增分区因为分区名不确定，需要根据分区的最大值来获取分区名
V_PARTNAME DBA_TAB_PARTITIONS.PARTITION_NAME%TYPE; –分区名
V_HIGHMONTH NVARCHAR2(10); –最大分区值截取成月份格式
V_CURRMONTH NVARCHAR2(10); –当前月
V_OLDMONTH NVARCHAR2(10); –6个月前的月份
V_SQL1 NVARCHAR2(1000); –移除旧分区的sql
V_SQL2 NVARCHAR2(1000); –缓存新分区的sql
V_SQL VARCHAR2(1000); –获取分区名和分区最大值的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 –循环操作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||\’ 需移除分区名称:\’||V_PARTNAME||\’ 分区最大值:\’||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||\’ 需缓存分区名称:\’||V_PARTNAME||\’ 分区最大值:\’||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，具体到线上改成执行就OK了
刚接触PLSQL，游标什么的也刚接触，只是自己做个简单记录
代码运行结果示例：

SQL> exec partition_cache
表名:TABLE1   需缓存分区名称:SYS_P1167   分区最大值:2018-07
SQL: ALTER TABLE CCPS.TABLE1 MODIFY PARTITION SYS_P1167 INMEMORY PRIORITY CRITICAL
表名:TABLE1   需移除分区名称:SYS_P388   分区最大值:2017-12
SQL: ALTER TABLE CCPS.TABLE1 MODIFY PARTITION SYS_P388 NO INMEMORY

*******************************************
表名:TABLE2   需移除分区名称:SYS_P588   分区最大值:2017-12
SQL: ALTER TABLE CCPS.TABLE2 MODIFY PARTITION SYS_P588 NO INMEMORY

*******************************************
表名:TABLE3   需缓存分区名称:SYS_P1168   分区最大值:2018-07
SQL: ALTER TABLE CCPS.TABLE3 MODIFY PARTITION SYS_P1168 INMEMORY PRIORITY CRITICAL
表名:TABLE3   需移除分区名称:SYS_P328   分区最大值:2017-12
SQL: ALTER TABLE CCPS.TABLE3 MODIFY PARTITION SYS_P328 NO INMEMORY

*******************************************
表名:TABLE4   需移除分区名称:SYS_P368   分区最大值:2017-12
SQL: ALTER TABLE CCPS.TABLE4 MODIFY PARTITION SYS_P368 NO INMEMORY

*******************************************
表名:TABLE5   需缓存分区名称:SYS_P1166   分区最大值:2018-07
SQL: ALTER TABLE CCPS.TABLE5 MODIFY PARTITION SYS_P1166 INMEMORY PRIORITY CRITICAL
表名:TABLE5   需移除分区名称:SYS_P428   分区最大值:2017-12
SQL: ALTER TABLE CCPS.TABLE5 MODIFY PARTITION SYS_P428 NO INMEMORY

*******************************************
PL/SQL procedure successfully completed