[大数据]番外-oracle数据库自动对数据库过期数据清除

照着这篇文章来的
原理：通过定时器调用数据清理的存储过程

1建立数据库任务配置表

SQL语句：

create table SYS_DBA_CONFIG
(
  NAME    VARCHAR2(250) not null,
  value   VARCHAR2(250) not null,
  type    VARCHAR2(200) not null,
  REMARK  VARCHAR2(200)  not null
  ISRUN   NUMBER(1) not null,
  COLUMNTYPE VARCHAR2(200)
);

配置表字段说明：

2，创建数据清理的存储过程（根据保留天数删除数据）

SQL语句：

CREATE OR REPLACE PROCEDURE PSH."DBA_AUTODELETE" AS
  /*******************************************************
  功能：根据保留天数删除数据V1.0
  说明：表名称、保存天数从配置表SYS_DBA_CONFIG读取
  ********************************************************/
  v_name         varchar2(250); --对应的表名
  v_remark       varchar2(250); --对应的表字段
  v_value        number(10); --表对应的表数据天数
  v_endTime      date; --清除的具体时间
  v_sql       varchar2(250);
  v_columntype   varchar2(20);
  CURSOR CS IS SELECT Name,VALUE,remark,columntype FROM SYS_DBA_CONFIG WHERE LOWER(TYPE)='auto_delete_table' AND ISRUN=1;
BEGIN
OPEN CS;
LOOP
FETCH CS INTO v_name, v_value,v_remark,v_columntype;--获得当前记录的数据
  EXIT WHEN CS%NOTFOUND;
  dbms_output.put_line('删除的表名：' || v_name||'  保留天数：'||v_value);
  v_endTime:=TRUNC(SYSDATE- v_value);
  if v_columntype='DATE' then
  v_sql := 'delete from ' || v_name||' where '|| v_remark||' <  TRUNC(SYSDATE- '||v_value||')' ;
  dbms_output.put_line('删除的sql：' || v_sql);
  else
  v_sql := 'delete from ' || v_name||' where to_date('||v_remark||',''yyyy-mm-dd hh24:mi:ss'') <  TRUNC(SYSDATE- '||v_value||')';
  dbms_output.put_line('删除的sql：' || v_sql);
  end if;
  begin
   execute immediate v_sql;
  end;
END LOOP;

END DBA_AutoDelete;

主要是要执行这一条SQL语句，可以根据自己需要自己修改

 v_sql := 'delete from ' || v_name||' where to_date('||v_remark||',''yyyy-mm-dd hh24:mi:ss'') <  TRUNC(SYSDATE- '||v_value||')';

执行完毕后，如果你使用的是navicat，那么会在这里看到

3，创建Job定时器

这里写时间间隔为一分钟是为了马上看到删除结果。
时间间隔当然还有其他的，比如：
每隔10天执行一次

REPEAT_INTERVAL => 'FREQ=DAILY; INTERVAL=10’;

每月15号执行一次

REPEAT_INTERVAL => 'FREQ=MONTHLY; BYMONTHDAY=15’;

每月最后一天执行一次

REPEAT_INTERVAL => 'FREQ=MONTHLY; BYMONTHDAY=-1';

就不一一列举了
SQL语句：

begin       
dbms_scheduler.create_job 
( 
job_name => 'Job_DBA_AUTODELETE', 
job_type => 'PLSQL_BLOCK', 
job_action => 'begin DBA_AUTODELETE; end;',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=1;', 
enabled => true
); 
end;

当执行完整个SQL语句之后，定时器就开始工作了，我们的目的也达到了。

4，Job定时器的一些方法

1，查询所有job任务

select * from dba_scheduler_jobs;

2，启用job任务（根据名称，比如刚刚创建的Job_DBA_AUTODELETE）

begin
	dbms_scheduler.run_job('Job_DBA_AUTODELETE',FALSE);
	end;

3，停用job任务

begin
	dbms_scheduler.stop_job(job_name => 'Job_DBA_AUTODELETE',force => TRUE);
	end;

4，删除job任务

begin
	dbms_scheduler.drop_job(job_name => 'Job_DBA_AUTODELETE',force => TRUE);
	end;

5，批量删除大量数据

照的这篇文章
不到30万条，不到20秒，可能有更快的方法，现在没有找到。

DECLARE
	--声明一个主键的集合类型
	TYPE ids_tbl IS TABLE OF MD_IOT_TAGHISTORY.NVFID%TYPE;
	id_list ids_tbl;
	INC NUMBER;
	LEN NUMBER:=5000;
	--创建游标
	CURSOR cur_cdd IS SELECT t.nvfid FROM MD_IOT_TAGHISTORY t WHERE T.SAVETIME< add_months(sysdate,-6);
BEGIN
--打开游标
OPEN cur_cdd;
LOOP
	--游标中的值取出来填充到id_list中，最多填充5000个
	FETCH cur_cdd 
	BULK COLLECT INTO id_list
	LIMIT LEN;
	
	-- 循环5000次，根据主键清理数据
	FORALL i IN 1..id_list.count   	 
		DELETE FROM MD_IOT_TAGHISTORY t where t.NVFID=id_list(i);
	--每5000条提交一次事务
    commit;
    --游标中取不出来东西的时候，结束循环
    EXIT WHEN cur_cdd%NOTFOUND;
END LOOP;
--关闭游标
CLOSE cur_cdd;
dbms_output.put_line('finished!');
--异常处理
exception
   when no_data_found then 
    dbms_output.put_line('未找到数据');
END;