oracle定时器定时清理某张表指定日期前的数据

--创建存储过程，检查表存不存在，存在就drop掉。 个人认为drop没有mysql方便

create or replace procedure tableExistThenDrop(tablename in varchar2) authid current_user
as
tempsql varchar2(2000);
vflag integer;
begin
  vflag:=0;
  tempsql:='select count(*) from user_tables where table_name = '''||UPPER(tablename)||'''';
  execute immediate tempsql into vflag;
  if vflag = 1 then 
    tempsql:='drop table '||UPPER(tablename);
    execute immediate tempsql;
  end if;
end;
/

--创建存储过程删除某个表中某一天之前的数据
create or replace procedure proce_delvertable authid current_user
as
tempsql varchar2(3000);
temp_date varchar2(20);
begin
  temp_date:=to_char(sysdate,'yyyy-MM-dd');
  
  tableExistThenDrop('user_bak');
  tempsql:='create table user_bak nologging as select * from user';
  execute immediate tempsql;
  
  tempsql:='truncate table user';
  execute immediate tempsql;
  
  tempsql:='insert into user select * from user_bak where dctime >='''||temp_date||' 00:00:00''';
  execute immediate tempsql;
  commit;
end;
/

--用户授权，如果不授权，DDL语句在job中执行不了
Grant Create Table,Create sequence, Create trigger, 
Create procedure, Drop any sequence, Create public synonym, 
Drop public synonym to scott;

--创建job，每周一早上8点执行
declare
  job number;
  BEGIN
     sys.dbms_job.submit(job => job,
                    what => 'proce_delvertable;',
                    next_date => TRUNC(next_day(sysdate,'星期一'))+8/24,
                    interval => 'TRUNC(next_day(sysdate,''星期一''))+8/24');
    COMMIT;

    DBMS_JOB.RUN(job);
  end;

Tips:在这个方案中，使用的是create table as select 的方式进行备份的，如果原表数据量大建议用此备份，因为如果直接delete原表，可能会造成锁表导致正常业务阻塞，而且这种方式的速度远比delete快。