oracle定时job，定时删除分区

create table t_partition
(
  parti_name VARCHAR2(20),
  table_name VARCHAR2(200)
);

create or replace procedure P_DEL_TEST1_PARTITION is
  v_error      varchar2(1000);
  v_cnt        number(10);
  v_cnt1       number(10);
  maxnum       number(10);
  v_parti_name varchar2(10);
begin

  maxnum := 10;

  --查询当前表上分区的数量
  execute immediate 'select count(*) from all_tab_partitions t where upper(table_name)= ''T_TEST1'''
    into v_cnt;

  --保存maxnum + 1个分区
  if v_cnt > maxnum + 1 then
    execute immediate 'delete from t_partition where upper(table_name)= ''T_TEST1''';
    commit;
    insert into t_partition
      select t.SUBOBJECT_NAME, 'T_TEST1' as table_name
        from user_objects t
       where upper(t.object_name) = 'T_TEST1'
         and t.GENERATED = 'Y'
         and t.OBJECT_TYPE = 'TABLE PARTITION';
    commit;
 
    v_cnt1 := 1;
    --循环查询当前分区的数据量并删除过期分区
    for v_cnt1 in 1 .. v_cnt - maxnum - 1 loop
   
      select t.parti_name
        into v_parti_name
        from (select t1.parti_name, table_name, rownum as rn
                from t_partition t1) t
       where t.table_name = 'T_TEST1'
         and t.rn = v_cnt1
       order by to_number(substr(t.parti_name,
                                 instr(t.parti_name, '_') + 2,
                                 length(t.parti_name))) ASC;
   
      ---删除分区
      execute immediate 'alter table T_TEST1 drop partition ' ||
                        v_parti_name || '';
   
    end loop;
  else
    dbms_output.put_line('the T_TEST1 partition need not to be cleaned');
  end if;

  --记录报错信息
exception
  when others then
    v_error := sqlerrm;
    insert into system.for_jobalarm_log
      (procedures_name, jobowner, logdate, logdesc)
    values
      ('T_TEST1', user, sysdate, v_error);
    commit;
end P_DEL_TEST1_PARTITION;
/