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

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

点击(此处)折叠或打开

1. CREATE OR REPLACE PROCEDURE PARTITION_CACHE
   
2. IS
   
3. TYPE REF_CURSOR_TYPE IS REF CURSOR;
   
4. C1 REF_CURSOR_TYPE; --动态游标，用来循环处理需要缓存的5张表
   
5. V_HIGHVALUE DBA_TAB_PARTITIONS.HIGH_VALUE%TYPE; --自增分区因为分区名不确定，需要根据分区的最大值来获取分区名
   
6. V_PARTNAME DBA_TAB_PARTITIONS.PARTITION_NAME%TYPE; --分区名
   
7. V_HIGHMONTH NVARCHAR2(10); --最大分区值截取成月份格式
   
8. V_CURRMONTH NVARCHAR2(10); --当前月
   
9. V_OLDMONTH NVARCHAR2(10); --6个月前的月份
   
10. V_SQL1 NVARCHAR2(1000); --移除旧分区的sql
    
11. V_SQL2 NVARCHAR2(1000); --缓存新分区的sql
    
12. V_SQL VARCHAR2(1000); --获取分区名和分区最大值的sql
    
13. 
    
14. BEGIN
    
15. V_CURRMONTH := TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYYY-MM');
    
16. V_OLDMONTH := TO_CHAR(ADD_MONTHS(SYSDATE,-6),'YYYY-MM');
    
17. 
    
18. DECLARE CURSOR C2 IS SELECT * FROM DBA_TABLES WHERE TABLE_NAME IN ('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5') ORDER BY TABLE_NAME;
    
19. BEGIN
    
20. FOR TABNAME IN C2 --循环操作5张表
    
21. LOOP
    
22. V_SQL := 'SELECT HIGH_VALUE,PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = '''|| TABNAME.TABLE_NAME||''' ORDER BY PARTITION_POSITION DESC';
    
23. OPEN C1 FOR V_SQL;
    
24. FETCH C1 INTO V_HIGHVALUE,V_PARTNAME;
    
25. WHILE (C1%FOUND) LOOP
    
26. V_HIGHMONTH := SUBSTR(V_HIGHVALUE,12,7);
    
27. IF (V_HIGHMONTH = V_OLDMONTH) THEN
    
28. DBMS_OUTPUT.PUT_LINE('表名:'||TABNAME.TABLE_NAME||' 需移除分区名称:'||V_PARTNAME||' 分区最大值:'||V_OLDMONTH);
    
29. V_SQL1 := 'ALTER TABLE CCPS.'||TABNAME.TABLE_NAME||' MODIFY PARTITION '||V_PARTNAME||' NO INMEMORY';
    
30. DBMS_OUTPUT.PUT_LINE('SQL: '||V_SQL1);
    
31. ELSIF (V_HIGHMONTH = V_CURRMONTH) THEN
    
32. DBMS_OUTPUT.PUT_LINE('表名:'||TABNAME.TABLE_NAME||' 需缓存分区名称:'||V_PARTNAME||' 分区最大值:'||V_HIGHMONTH);
    
33. V_SQL2 := 'ALTER TABLE CCPS.'||TABNAME.TABLE_NAME||' MODIFY PARTITION '||V_PARTNAME||' INMEMORY PRIORITY CRITICAL';
    
34. DBMS_OUTPUT.PUT_LINE('SQL: '||V_SQL2);
    
35. END IF;
    
36. FETCH C1 INTO V_HIGHVALUE,V_PARTNAME;
    
37. END LOOP;
    
38. CLOSE C1;
    
39. DBMS_OUTPUT.PUT_LINE(' ');
    
40. DBMS_OUTPUT.PUT_LINE('*******************************************');
    
41. END LOOP;
    
42. END;
    
43. 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