背景介绍
ORACLE按照日期创建分区表,仅保留一年数据,每天清理一年前的分区数据。
实现方式
每次清理均处理一年前全部分区,即时某次调度执行失败,也不会造成垃圾数据堆积。
示例代码
--创建存储过程
create or replace procedure zxjy_ora.del_wen_pt
as
v_sql1 varchar2(2000);
begin
FOR cur IN (select PARTITION_NAME from user_tab_partitions where table_name='WEN' AND substr(partition_name,2,8)<=to_char(sysdate-366,'yyyymmdd')) LOOP
v_sql1:='alter table ZXJY_ORA.WEN drop partition '||cur.pname||' ';
execute immediate v_sql1;
END LOOP;
end del_wen_pt;
/
---创建调度
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'sch_del_wen_pt',
start_date => SYSDATE,
repeat_interval => 'FREQ=DAILY; BYHOUR=22;BYMINUTE=30;BYSECOND=0',
comments => 'EACH ONE DAYS delete A old PARTITION ');
end;
/
--创建作业执行调度
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'JOB_DEL_WEN_PT',
job_type => 'STORED_PROCEDURE',
job_action => 'zxjy_ora.del_wen_pt',
schedule_name => 'sch_del_wen_pt', enabled => true);
END;
/