oracle定期删除表分区存储过程

 

1. set serveroutput on  
2. dbms_output.put_line()
3. exec 存储过程

信息表： 

create table T_DROP_PAR_LIST
(
  ID               number not null,
  TABLE_NAME       VARCHAR2(60) not null,
  SAVE_DAYS        number not null,
  IS_DROP          char(1) not null,
  DROP_PAR_TIME    DATE

);
-- Add comments to the table 
comment on table T_DROP_PAR_LIST is '待删除表分区信息表';
-- Add comments to the columns 
comment on column T_DROP_PAR_LIST.ID
  is '序号ID';
comment on column T_DROP_PAR_LIST.TABLE_NAME
  is '表名';
comment on column T_DROP_PAR_LIST.SAVE_DAYS
  is '保留天数';
comment on column T_DROP_PAR_LIST.IS_DROP
  is '是否删除，1代表删除，0不删除';
comment on column T_DROP_PAR_LIST.DROP_PAR_TIME
  is '删除时间';

 

create or replace procedure DROP_PART_DAYS
AS
 v_SqlExec VARCHAR2(200); 

  --表的游标定义
  cursor tb_cursor is
    SELECT table_name,save_days from T_DROP_PAR_LIST where is_drop=1;  --记录要删除分区的表名，保留多长时间
    v_tb tb_cursor%rowtype;
  --表分区的游标定义
cursor par_cursor(tb_name varchar2,save_days number) is
  select partition_name
   from user_tab_partitions
   where table_name= tb_name and partition_name not like '%MAX%'
   and to_date(REPLACE(SUBSTR(partition_name,length(partition_name)-8,length(partition_name)),'_',''),'YYYYMMDD')< sysdate- save_days
   order by partition_name;
begin
  open   tb_cursor;
     loop
       fetch tb_cursor into v_tb;
       exit when tb_cursor%notfound;
           for par_cur in par_cursor(v_tb.table_name,v_tb.save_days) 
             loop
              v_SqlExec:='ALTER TABLE '|| v_tb.table_name ||' DROP PARTITION '|| par_cur.partition_name ;
             -- dbms_output.put_line(v_SqlExec);
              DBMS_Utility.Exec_DDL_Statement(v_SqlExec);
             end loop;
              update t_drop_par_list set drop_par_time = sysdate where table_name=v_tb.table_name ;
              commit;
      end loop;
  close tb_cursor;

 END DROP_PART_DAYS;