oracle月分区自动增长

颛孙3709

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;