Job定时删除数据 需求：对一个表，每天删除一月前的历史数据 思路 1.编写SQL，删除一月前的历史数据，使用函数取值 2.测试JOB创建，查询，维护，管理 3.测试布置job,满足效果 ***测试数据准备 select dbms_metadata.get_ddl('TABLE','xxx','IMAGE_APP') ddl_text from dual DDL_TEXT -------------------------------------------------------------------------------- create tablespace xxx datafile '/home/oracle/test1.dbf' size 1m autoextend on next 10m maxsize unlimited; create tablespace xxxdatafile '/home/oracle/test2.dbf' size 1m autoextend on next 10m maxsize unlimited; create user xxxidentified by abc default tablespace IMAGE_APP_TBS; grant dba to xxx; CREATE TABLE "xxx"."IMAGE_CLEAN_LOG" ( "IMAGE_CLEAN_LOG_ID" NUMBER(12,0) NOT NULL ENABLE, "IMAGE_PIC_ID" NUMBER(12,0), "CLEAN_TIME" TIMESTAMP (6), "CACHE_CODE" CHAR(10), "SUCCESS_FLAG" CHAR(1), "FAIL_REASON_ID" CHAR(1), "FAIL_REASON_DESC" VARCHAR2(400), CONSTRAINT "PK_IMAGE_CLEAN_LOG" PRIMARY KEY ("IMAGE_CLEAN_LOG_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "xxx" ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "xxx"; CREATE INDEX "xxx"."I_ICL_CLEAN_TIME" ON "xxx"."IMAGE_CLEAN_LOG" ( "CLEAN_TIME") TABLESPACE "xxx" global PARTITION BY HASH ("CLEAN_TIME") PARTITIONS 8; insert into "xxx"."IMAGE_CLEAN_LOG" (IMAGE_CLEAN_LOG_ID,IMAGE_PIC_ID,
CLEAN_TIME,CACHE_CODE,SUCCESS_FLAG,FAIL_REASON_ID,FAIL_REASON_DESC) select rownum,rownum, to_date(to_char(sysdate-365,'J')+ trunc(dbms_random.value(0,365)),'J'), 'a', 'b', 'c', rpad('*',400,'*') from dual connect by rownum <= 100000; commit; SQL> select to_char(CLEAN_TIME,'yyyymm'),count(*) from "IMAGE_APP"."IMAGE_CLEAN_LOG"
group by to_char(CLEAN_TIME,'yyyymm') order by 1; TO_CHA COUNT(*) ------ ---------- 201710 1414 201711 8150 201712 8374 201801 8298 201802 7615 201803 8533 201804 8204 201805 8560 201806 8389 201807 8483 201808 8389 201809 8300 201810 7291 13 rows selected. 1.编写SQL，删除一月前的历史数据，使用函数取值 SQL> select sysdate,add_months(sysdate,1),add_months(sysdate,-1),trunc(add_months(sysdate,1)) from dual; SYSDATE ADD_MONTHS(SYSDATE, ADD_MONTHS(SYSDATE, TRUNC(ADD_MONTHS(SY ------------------- ------------------- ------------------- ------------------- 2018-10-27 10:34:19 2018-11-27 10:34:19 2018-09-27 10:34:19 2018-11-27 00:00:00 SQL> select sysdate-1,add_months(sysdate,-2),add_months(sysdate,-1) from dual; SYSDATE-1 ADD_MONTH ADD_MONTH --------- --------- --------- 26-OCT-18 27-AUG-18 27-SEP-18 SYSDATE-1 ADD_MONTHS(SYSDATE, ADD_MONTHS(SYSDATE, ------------------- ------------------- ------------------- 2018-10-26 11:04:27 2018-08-27 11:04:27 2018-09-27 11:04:27 --测试between and create table a(id int); insert into a values(1); insert into a values(2); insert into a values(3); insert into a values(4); select * from a where id between 1 and 3; ID ---------- 1 2 3 delete "xxx"."IMAGE_CLEAN_LOG" where CLEAN_TIME between add_months(sysdate,-2) and add_months(sysdate,-1); select count(*) from "xxx"."IMAGE_CLEAN_LOG" where CLEAN_TIME between add_months(sysdate,-2) and add_months(sysdate,-1); COUNT(*) ---------- 0 --OK alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; spool /home/oracle/work_dir/yz/delete_month_20181025.log select 'delete' as execute_script,sysdate from dual; select sysdate from dual; set timing on set lines 600 pages 300 set serveroutput on declare begin_date date:=to_date('2011-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss'); end_date date; v_sql varchar2(4000); v_out date; begin for i in 1..100 loop select sysdate into v_out from dual; end_date:=add_months(begin_date,1); v_sql:='delete from xxx.IMAGE_CLEAN_LOG t
where CLEAN_TIME >= to_date('''||begin_date ||''',''yyyy-mm-dd hh24:mi:ss'')
and CLEAN_TIME < to_date('''||end_date||''',''yyyy-mm-dd hh24:mi:ss'')'; dbms_output.put_line(begin_date); execute immediate v_sql; dbms_output.put_line(v_sql); sys.dbms_output.put_line(v_out); commit; begin_date:=add_months(begin_date,1); exit when begin_date>=to_date('2018-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss'); end loop; end; / select sysdate from dual; spool off exit select count(*) from xxx.IMAGE_CLEAN_LOG where CLEAN_TIME>=to_date('2018-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss'); COUNT(*) ---------- 8145 select count(*) from xxx.IMAGE_CLEAN_LOG where CLEAN_TIME> add_months(sysdate,-1); COUNT(*) ---------- 5351 delete "xxx"."IMAGE_CLEAN_LOG" where CLEAN_TIME between add_months(sysdate,-2) and add_months(sysdate,-1); SQL> select count(*) from xxx.IMAGE_CLEAN_LOG where CLEAN_TIME>=to_date('2018-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss'); COUNT(*) ---------- 5351 SQL> roll; Rollback complete. SQL> select count(*) from xxx.IMAGE_CLEAN_LOG where CLEAN_TIME>=to_date('2018-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss'); COUNT(*) ---------- 8145 2.测试JOB创建，查询，维护，管理 create or replace procedure delete_log as begin delete "xxx"."IMAGE_CLEAN_LOG" where CLEAN_TIME between add_months(sysdate,-2) and add_months(sysdate,-1); commit; end; / exec delete_log declare job number; BEGIN DBMS_JOB.SUBMIT(JOB => job,/*自动生成JOB_ID*/ WHAT=>'delete_log;',/*需要执行的存储过程名称或SQL语句*/ NEXT_DATE=>sysdate,/*初次执行时间-立即执行*/ INTERVAL=>'TRUNC(sysdate+1)+1/24'); /*每天凌晨一点执行一次*/ end; / select job,SCHEMA_USER,LAST_DATE,NEXT_DATE,BROKEN,INTERVAL,WHAT,FAILURES from dba_jobs where what like 'delete%'; JOB SCHEMA_USE LAST NEXT_DATE B INTERVAL WHAT FAILURES ---------- ---------- ---- ------------------- - ------------------------- --------------------- 4 SYS 2018-10-27 11:42:38 N TRUNC(sysdate+1)+1/24 delete_log; -- begin DBMS_JOB.RUN(4); /*40 job的id*/ end; SQL> select count(*) from xxx.IMAGE_CLEAN_LOG where CLEAN_TIME>=to_date('2018-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss'); COUNT(*) ---------- 5351 --JOB调用成功 select job,SCHEMA_USER,LAST_DATE,NEXT_DATE,BROKEN,INTERVAL,WHAT,FAILURES from dba_jobs where what like 'delete%'; JOB SCHEMA_USE LAST_DATE NEXT_DATE B INTERVAL WHAT FAILURES ---- ---------- ------------------------------ ------------------- - ------------------------- ------------- ---------- 4 SYS 2018-10-27 11:47:32 2018-10-28 01:00:00 N TRUNC(sysdate+1)+1/24 delete_log; 0 ***********以上测试初步成功**** 如下再次测试JOB的自动效果 declare job number; BEGIN DBMS_JOB.SUBMIT( JOB => job, /*自动生成JOB_ID*/ WHAT => 'delete_log;', /*需要执行的存储过程名称或SQL语句*/ NEXT_DATE => sysdate, /*初次执行时间-立即执行*/ INTERVAL => 'sysdate+1/1440' /*每分钟执行一次*/ ); commit; end; / begin DBMS_JOB.RUN(6); /*40 job的id*/ end; *删除JOB begin dbms_job.remove(5); /*删除自动执行的job,参数是 job的id*/ commit; end; SQL> select job,SCHEMA_USER,LAST_DATE,NEXT_DATE,BROKEN,INTERVAL,WHAT,FAILURES from dba_jobs where what like 'delete%'; JOB SCHEMA_USE LAST_DATE NEXT_DATE B INTERVAL WHAT FAILURES ---------- ---------- ------------------------------ ------------------- - ------------------------- ------------- ---------- 4 SYS 2018-10-27 11:47:32 2018-10-28 01:00:00 N TRUNC(sysdate+1)+1/24 delete_log; 0 6 SYS 2018-10-27 11:52:46 2018-10-27 11:53:46 N sysdate+1/1440 delete_log; 0 Elapsed: 00:00:00.00 SQL> select count(*) from xxx.IMAGE_CLEAN_LOG where CLEAN_TIME>=to_date('2018-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss'); COUNT(*) ---------- 5351 插入测试数据 create table abc as select * from xxx.IMAGE_CLEAN_LOG; update abc set CLEAN_TIME=CLEAN_TIME-30; commit; select count(*) from abc where CLEAN_TIME between
to_date('2018-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018-09-27 00:00:00','yyyy-mm-dd hh24:mi:ss'); COUNT(*) ---------- 4547 insert into xxx.IMAGE_CLEAN_LOG select * from abc; commit; select count(*) from xxx.IMAGE_CLEAN_LOG where CLEAN_TIME
between to_date('2018-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018-09-27 00:00:00','yyyy-mm-dd hh24:mi:ss'); COUNT(*) ---------- 4547 --ALTER TABLE IMAGE_APP.IMAGE_CLEAN_LOG disable CONSTRAINT PK_IMAGE_CLEAN_LOG; ORA-00001: unique constraint (IMAGE_APP.PK_IMAGE_CLEAN_LOG) violated --测试发现，自动执行JOB，且成功删除数据 SQL> select sysdate from dual; SYSDATE ------------------- 2018-10-27 12:09:36 Elapsed: 00:00:00.00 SQL> select count(*) from xxx.IMAGE_CLEAN_LOG where CLEAN_TIME
between to_date('2018-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018-09-27 00:00:00','yyyy-mm-dd hh24:mi:ss'); COUNT(*) ---------- 0 SQL> select job,SCHEMA_USER,LAST_DATE,NEXT_DATE,BROKEN,INTERVAL,WHAT,FAILURES from dba_jobs where what like 'delete%'; JOB SCHEMA_USE LAST_DATE NEXT_DATE B INTERVAL WHAT FAILURES ---------- ---------- ------------------------------ ------------------- - ------------------------- ------------- ---------- 4 SYS 2018-10-27 11:47:32 2018-10-28 01:00:00 N TRUNC(sysdate+1)+1/24 delete_log; 0 6 SYS 2018-10-27 12:09:36 2018-10-27 12:10:36 N sysdate+1/1440 delete_log; 0 3.测试布置job,满足效果 create or replace procedure delete_log as begin delete "xxx"."IMAGE_CLEAN_LOG" where CLEAN_TIME between add_months(sysdate,-2) and add_months(sysdate,-1); commit; end; / declare job number; BEGIN DBMS_JOB.SUBMIT( JOB => job, /*自动生成JOB_ID*/ WHAT => 'delete_log;', /*需要执行的存储过程名称或SQL语句*/ NEXT_DATE => sysdate, /*初次执行时间-立即执行*/ INTERVAL => 'TRUNC(sysdate+1)+1/24' /*每天凌晨一点执行一次*/ ); --commit; end; / --查询JOB信息 SQL> select job,SCHEMA_USER,LAST_DATE,NEXT_DATE,BROKEN,INTERVAL,WHAT,FAILURES from dba_jobs where what like 'delete%'; --第一次需要手动执行 begin DBMS_JOB.RUN(4); /*40 job的id*/ end; --再次查询JOB自动调用时间 SQL> select job,SCHEMA_USER,LAST_DATE,NEXT_DATE,BROKEN,INTERVAL,WHAT,FAILURES from dba_jobs where what like 'delete%'; ---------------------------------------------------------------------------------------------------------- --记录数量 create table job_delete_log_image(d_date date,n_type varchar2(20),n_sum int) tablespace IMAGE_APP_TBS; create or replace procedure delete_log as begin insert into job_delete_log_image select sysdate,'old_delete_count',count(*)
from "xxx"."IMAGE_CLEAN_LOG" where CLEAN_TIME<sysdate-31; delete "xxx"."IMAGE_CLEAN_LOG" where CLEAN_TIME<sysdate-31; insert into job_delete_log_image select sysdate,'new_delete_count',count(*)
from "xxx"."IMAGE_CLEAN_LOG" where CLEAN_TIME<sysdate-31; commit; end; / exec delete_log; **** -------------------------------------------------------------------------------------- truncate table job_delete_log_image; truncate table "xxx"."IMAGE_CLEAN_LOG"; insert into "xxx"."IMAGE_CLEAN_LOG"
(IMAGE_CLEAN_LOG_ID,IMAGE_PIC_ID,CLEAN_TIME,
CACHE_CODE,SUCCESS_FLAG,
FAIL_REASON_ID,FAIL_REASON_DESC) select rownum,rownum, to_date(to_char(sysdate-365,'J')+ trunc(dbms_random.value(0,365)),'J'), 'a', 'b', 'c', rpad('*',400,'*') from dual connect by rownum <= 100000; commit; ******* 记录日志 创建日志表 --drop table job_delete_log_image purge; create table job_delete_log_image (id int, job_stime date, job_etime date, job_sql varchar2(2000), rows_deleted int, row_check int ) tablespace xxx; create index job_delete_index on job_delete_log_image(id); set serveroutput on create or replace procedure delete_log as v_sql varchar2(2000); sql_rowcount number; sequence_id int; delete_date varchar2(2000); begin select to_char(trunc(sysdate-31),'yyyymmdd hh24:mi:ss') into delete_date from dual; insert into job_delete_log_image(id,job_stime) select to_char(sysdate,'yyyymmddhh24miss'),sysdate from dual; v_sql:='delete "xxx"."IMAGE_CLEAN_LOG" where CLEAN_TIME<to_date('''||delete_date||''',''yyyymmdd hh24:mi:ss'')'; execute immediate v_sql; sql_rowcount := sql%rowcount; select max(id) into sequence_id from job_delete_log_image; update job_delete_log_image
set job_etime=sysdate,
job_sql=v_sql,
rows_deleted=sql_rowcount,
row_check=
(select count(*) from "xxx"."IMAGE_CLEAN_LOG"
where CLEAN_TIME<to_date(delete_date,'yyyymmdd hh24:mi:ss')) where id=sequence_id; commit; end delete_log; / exec delete_log select * from job_delete_log_image;