根据时间分批删除历史数据

根据时间分批删除历史数据

-- 存储过程创建
---------------------------------------------
create or replace procedure prc_delete_by_day(tab_name in varchar2,date_column in varchar2,date_end in date,interval in number)
/*
 *根据时间字段清理历史数据
 * tab_name		需清理历史数据的表
 * date_column	根据该字段的值进行清理
 * date_end		清理该天之前的数据	
 * interval		每次清理的时间间隔
*/
is
v_begin date;
v_end   date :=trunc(date_end);
sql1 varchar2(1000);
sql2 varchar2(1000);
sql3 varchar2(1000);
begin
sql1:='select trunc(min('||date_column||')) from '||tab_name;
execute immediate sql1 into v_begin;
while v_begin<=v_end
loop
  sql2:= 'delete from '||tab_name||' where '||date_column||' < '''||v_begin||'''';
  execute immediate sql2;
  commit;
  v_begin:=v_begin+interval;
end loop;
sql3:= 'delete from '||tab_name||' where '||date_column||' < '''||v_end||'''';
execute immediate sql3;
commit;
end;
/

-- 使用案例：
---------------------------------------------
create table t1 (id number,create_time date);
insert into t1 values(1,sysdate-7);
insert into t1 values(1,sysdate-5);
insert into t1 values(1,sysdate-3);
insert into t1 values(1,sysdate);
insert into t1 values(2,sysdate);
commit;

BEGIN
  prc_delete_by_day('T1','CREATE_TIME',sysdate,1);
END;

-- 表分析
---------------------------------------------
BEGIN
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname=> 'T1', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
END;