温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

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

发布时间:2020-08-17 06:20:12 来源:ITPUB博客 阅读:338 作者:xjlovezxx 栏目:关系型数据库
    最近为了使用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. BEGIN
  14.   V_CURRMONTH := TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYYY-MM');
  15.   V_OLDMONTH := TO_CHAR(ADD_MONTHS(SYSDATE,-6),'YYYY-MM');
  16.   
  17.   DECLARE CURSOR C2 IS SELECT * FROM DBA_TABLES WHERE TABLE_NAME IN ('TABLE1','TABLE2','TABLE3','TABLE4','TABLE5') ORDER BY TABLE_NAME;
  18.   BEGIN
  19.   FOR TABNAME IN C2       --循环操作5张表
  20.     LOOP
  21.       V_SQL := 'SELECT HIGH_VALUE,PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = '''|| TABNAME.TABLE_NAME||''' ORDER BY PARTITION_POSITION DESC';
  22.       OPEN C1 FOR V_SQL;
  23.       FETCH C1 INTO V_HIGHVALUE,V_PARTNAME;
  24.       WHILE (C1%FOUND) LOOP
  25.            V_HIGHMONTH := SUBSTR(V_HIGHVALUE,12,7);
  26.            IF (V_HIGHMONTH = V_OLDMONTH) THEN
  27.               DBMS_OUTPUT.PUT_LINE('表名:'||TABNAME.TABLE_NAME||' 需移除分区名称:'||V_PARTNAME||' 分区最大值:'||V_OLDMONTH);
  28.               V_SQL1 := 'ALTER TABLE CCPS.'||TABNAME.TABLE_NAME||' MODIFY PARTITION '||V_PARTNAME||' NO INMEMORY';
  29.               DBMS_OUTPUT.PUT_LINE('SQL: '||V_SQL1);
  30.            ELSIF (V_HIGHMONTH = V_CURRMONTH) THEN
  31.               DBMS_OUTPUT.PUT_LINE('表名:'||TABNAME.TABLE_NAME||' 需缓存分区名称:'||V_PARTNAME||' 分区最大值:'||V_HIGHMONTH);
  32.               V_SQL2 := 'ALTER TABLE CCPS.'||TABNAME.TABLE_NAME||' MODIFY PARTITION '||V_PARTNAME||' INMEMORY PRIORITY CRITICAL';
  33.               DBMS_OUTPUT.PUT_LINE('SQL: '||V_SQL2);
  34.            END IF;
  35.            FETCH C1 INTO V_HIGHVALUE,V_PARTNAME;
  36.       END LOOP;
  37.     CLOSE C1;
  38.     DBMS_OUTPUT.PUT_LINE(' ');
  39.     DBMS_OUTPUT.PUT_LINE('*******************************************');
  40.     END LOOP;
  41.     END;
  42.   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

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI