Oracle存储过程实现自动删除分区和自动增加分区（不写日志）

create or replace procedure p_add_delete_partitions
(p_keep_month int --分区保留月份
)
as
  v_sql              varchar2(300);
  v_delete_part_date varchar2(64);
  v_add_part_date    varchar2(64);
  v_num               number default 0;
begin

  -- DROP指定月份之前的所有分区
  for i in (select table_name, min(partition_name) as min_part_name
              from user_tab_partitions
             where table_name in ('EV_COMBINED', 'ALARM')
             group by table_name) loop

    v_delete_part_date := substr(i.min_part_name,
                                 instr(i.min_part_name, '_',-1) + 1);

    while months_between(sysdate, to_date(v_delete_part_date, 'yyyymm')) > p_keep_month loop

            
      v_sql := 'ALTER TABLE ' || i.table_name || ' DROP PARTITION ' ||
               i.table_name || '_' || v_delete_part_date ||' update global indexes';
               
      execute immediate v_sql;
      
      v_delete_part_date := to_char(add_months(to_date(v_delete_part_date,'yyyymm'),1),'yyyymm');
      
    end loop;
  end loop;
--alter table ev_combined drop partition EV_COMBINED_202103 update global indexes
  -- add  partition
  for i in (select table_name, max(partition_name) as max_part_name
              from user_tab_partitions
             where table_name in ('EV_COMBINED', 'ALARM')
             group by table_name) loop

    v_add_part_date := substr(i.max_part_name,
                              instr(i.max_part_name, '_',-1) + 1);
  --当表的最大分区小于3个月后，就给表多新增3个月的分区
   if  months_between(to_date(v_add_part_date, 'yyyymm'),sysdate)  < 3 then
     for j in 0..3 loop
       v_num := v_num +1;
       v_add_part_date := to_char(add_months(to_date(v_add_part_date,'yyyymm'),1),'yyyymm');
        v_sql := 'ALTER TABLE ' || i.table_name || ' add PARTITION ' ||
                 i.table_name || '_' || v_add_part_date;
        execute immediate v_sql;
     end loop;
    end if;
  end loop;


end;
/

declare job_id int;
begin
select max(job) into job_id from user_jobs where WHAT='p_add_delete_partitions(12);';
if job_id>0 then
dbms_job.remove(job_id);
commit;
end if;
end;
/

DECLARE
JOB NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
JOB => JOB,
WHAT => 'p_add_delete_partitions(12);',
NEXT_DATE =>TRUNC(SYSDATE + 1) + 2/24,
INTERVAL => 'TRUNC(LAST_DAY(SYSDATE) + 1)',
no_parse => FALSE
);
END;
/