oracle月分区自动增长

最新推荐文章于 2023-09-09 08:00:00 发布

iteye_12784

最新推荐文章于 2023-09-09 08:00:00 发布

阅读量738

点赞数

分类专栏： oracle 文章标签： oracle sql

本文链接：https://blog.csdn.net/iteye_12784/article/details/82599741

版权

oracle 专栏收录该内容

6 篇文章 0 订阅

订阅专栏


CREATE OR REPLACE PROCEDURE PRC_ADD_PARTITION_MON
 IS
  --/************************************************************************************
  -- 程序名称:    PRC_ADD_PARTITION_MON
  -- 功能描述:    增加表月分区存储过程   -- 期达目的:    将此程序挂起  每月1自动在最大表分区上面加1个月 无需人为来操作
  -- 注意：       表分区里面切忌不要出现2个表空间 不然这个会有问题
  -- 输入参数:    'YYYYMM' - <作业的处理日期>
  -- 输出参数:    0 - <0为正常结束，其余为异常>
  -- 输入资源:
  -- 输出资源:
  -- 中间资源:    <用户名>.<中间表或视图等对象名>
  -- 创建人员:    XXXX
  -- 创建日期:    20120725
  -- 版本说明:    V1.0
  -- 修改人员:    (针对程序的任何修改都需要记录修改人员)
  -- 修改日期:    (针对程序的任何修改都需要记录修改日期)
  -- 修改原因:    (针对程序的任何修改都需要记录修改原因，如果多次修改需依次记录)
  -- 版本说明:
  -- 执行说明：
  -- 公司名称:    
  --/************************************************************************************
  --执行MM月的数据，输入的日期参数为YYYY(MM+1)DD，如'20091201'则是执行11月份的数据。

  -- 编写规则说明
  -- 说明1：所有自定义变量均用小写，并以v_打头；所有字段名均用大写
  -- 说明2: 所有关键值均用大字；模式名、表名、函数名、存储过程名均用大写
  -- 说明3: 缩进同其它程序，里面的逻辑体比外面的逻辑体向有缩进一个TAB键。
  --        对齐主要是针对SQL语句，采用关键字换行和右对齐的方式（如果不方便也可使用左对齐。
  -- 说明4: SQL语句目标和源要齐整
  -- 说明5: 对每个表的数据作改变后都要显示的提交
  v_sql       VARCHAR2(10000) DEFAULT ''; -- 动态SQL变量,注意SQL长度
  v_prc       VARCHAR2(40);
  v_date      VARCHAR2(8);
  v_stepnum   NUMBER          DEFAULT 0;           -- 运行步骤
  v_errmsg    VARCHAR2(1024)  DEFAULT '正常';
  TYPE V_CURTYPE IS REF CURSOR;
  v_open_cur  V_CURTYPE;  -- 定义游标 循环迭代
  v_table_name VARCHAR2(40); -- 表名
  v_table_owner VARCHAR2(40); -- 用户名
  v_max_part_mon VARCHAR2(40); -- 已经存在在系统里面的最大月份值
  v_tablespace_name VARCHAR2(40); -- 这个表分区的表空间
  v_max_next_mon VARCHAR2(40); -- 要拓展的表分区的月份
  v_max_next2_mon VARCHAR2(40);-- 要拓展的表分区的月份的下一个月

BEGIN
  v_prc:='PRC_ADD_PARTITION_MON';
  SELECT to_char(SYSDATE,'yyyymmdd') INTO v_date FROM  dual;
  --正文SQL处理

  --参考思路
  --先把表，表用户，表分区最大值，表分区所在表空间拿出来
  --循环迭代  取出这些表的月份的下一个月份值
  --取到下一个月的值 添加表分区

  -- 1 拿出需要拓展月分区的表的相关信息
     OPEN v_open_cur FOR
   'SELECT TABLE_NAME,TABLE_OWNER,MAX(SUBSTR(PARTITION_NAME,2,6)),TABLESPACE_NAME FROM ALL_TAB_PARTITIONS
   WHERE TABLE_OWNER=''JXZZJY''
   --AND TABLE_NAME LIKE ''MD%''
   AND LENGTH(RTRIM(PARTITION_NAME))=7
   GROUP BY TABLE_NAME,TABLE_OWNER,TABLESPACE_NAME';

  -- 开始把值赋给变量 准备循环迭代了
  LOOP
    FETCH v_open_cur INTO v_table_name,v_table_owner,v_max_part_mon,v_tablespace_name;
     EXIT WHEN v_open_cur%NOTFOUND;

     -- 把循环里面的最大月份加1 作为下一个月 为开始拓展表分区做好准备
     SELECT TO_CHAR(ADD_MONTHS(TO_DATE(v_max_part_mon,'YYYYMM'),1),'YYYYMM') INTO v_max_next_mon FROM DUAL;
     SELECT TO_CHAR(ADD_MONTHS(TO_DATE(v_max_part_mon,'YYYYMM'),2),'YYYYMM') INTO v_max_next2_mon FROM DUAL;

     -- 开始拓展表空间
       v_stepnum :=v_stepnum+1;
       v_sql:= 'ALTER TABLE '||v_table_owner||'.'||v_table_name||' ADD PARTITION P'||v_max_next_mon||' VALUES LESS THAN ('''||v_max_next2_mon||''') TABLESPACE '||v_tablespace_name||' ';
        EXECUTE IMMEDIATE v_sql;
        PRC_LOG_WRT(v_date,v_prc,v_stepnum,v_sql,v_errmsg,SQL%ROWCOUNT);
        COMMIT;

   END LOOP;
   CLOSE v_open_cur;


     -- 存储过程错误信息记录
  EXCEPTION
  WHEN OTHERS THEN

    v_errmsg := v_stepnum || '步出错：' || SQLERRM;
    PRC_LOG_WRT(v_date,v_prc,v_stepnum,v_sql,v_errmsg,-1);
    COMMIT;

END;