按日创建分区、清理旧分区的Stored Procedure执行无效果问题咨询

算力阿峰

2026-9-26

问题排查

无创建新分区逻辑：现有存储过程仅包含删除旧分区的代码片段，完全没有按日新增分区的实现，因此不可能自动生成新分区。
删除分区逻辑存在多处语法/逻辑错误：
1. 视图名拼写错误：查询分区的系统视图正确名称是user_tab_partitions（末尾带s），你写的user_tab_partition少了后缀s，会直接报视图不存在的错误。
2. 数据字典过滤规则错误：Oracle数据字典中user_tab_partitions的table_name字段默认存储为大写，你写的table_name='tbl1'会匹配不到任何记录，除非建表时强制指定了小写表名。
3. high_value处理逻辑完全错误：high_value是LONG类型，无法直接用substr截取，且你截取的位置和实际range分区的high_value格式完全不匹配（Oracle range分区的high_value默认是TO_DATE('xxxx-xx-xx', ...)的字符串形式，直接截取前4位得到的是TO_D而非年份）。
4. 动态SQL语法错误：execute immediate拼接的SQL中drop partition后缺少空格，最终生成的SQL是alter table tbl1 drop partition[分区名]，存在语法错误无法执行。
5. 变量类型定义错误：dt定义为varchar2类型，但to_date返回的是DATE类型，存在隐式转换风险，容易触发日期格式报错。

修复方案

因为你的分区命名格式固定为tbl_yyyymmdd，直接从分区名提取日期的逻辑更稳定，以下是修复后同时包含旧分区删除、新分区创建的完整代码：

create or replace procedure sp_partn as 
 v_partition_name varchar2(100);
 v_part_date date;
 v_sql varchar2(4000);
 v_next_part_date date := trunc(sysdate+1); -- 待创建的明日分区日期

 -- 删除9个月以上旧分区的过程
 procedure tbl1_prtn as
 begin
  for i in (select partition_name from user_tab_partitions where table_name='TBL1')
   loop
    -- 直接从分区名提取日期，适配tbl_yyyymmdd的命名格式
    begin
      v_part_date := to_date(substr(i.partition_name,5,8),'yyyymmdd');
    exception
      when others then
        continue; -- 分区名不符合格式的直接跳过
    end;
    
    if sysdate >= add_months(v_part_date, 9) then
      -- 增加update global indexes避免删除分区后全局索引失效，无全局索引可删除该参数
      v_sql := 'alter table TBL1 drop partition ' || i.partition_name || ' update global indexes';
      execute immediate v_sql;
    end if;
  end loop;
 end tbl1_prtn;

 -- 新增明日日分区的过程
 procedure add_tbl1_part as
 begin
  v_partition_name := 'TBL_'||to_char(v_next_part_date,'yyyymmdd');
  -- 先判断分区是否已存在，重复创建会报错
  for i in (select 1 from user_tab_partitions where table_name='TBL1' and partition_name=v_partition_name)
  loop
    return;
  end loop;
  -- 拼接建分区SQL，需将下方的CREATE_DT替换为你表实际的分区字段名
  v_sql := 'alter table TBL1 add partition '||v_partition_name||' values less than (to_date('''||to_char(v_next_part_date+1,'yyyy-mm-dd')||''',''yyyy-mm-dd''))';
  execute immediate v_sql;
 end add_tbl1_part;

begin
 tbl1_prtn;
 add_tbl1_part; -- 调用新增分区逻辑
end sp_partn;
/

内容的提问来源于stack exchange，提问作者Md Wasi