使用存储过程定期批量删除数据

一、环境准备

数据库中创建存放日志的路径：

create directory LOG_DIR as '/home/oracle/clean_log';
grant read,write on directory LOG_DIR to GAPS41;

二、创建日志存储过程

create or replace procedure gaps41.PUT_INTO_FILE(table_name  in varchar2,
                                                 l_delete    in number,
                                                 column_name in varchar2,
												 min_data	 in varchar2,
                                                 s_time      in varchar2,
                                                 e_time      in varchar2,
												 flag		 in varchar2) is
  file_handle utl_file.file_type;
  file_name   varchar2(20);
begin
  file_name   := to_char(sysdate, 'yyyymmdd') || '.log';
  file_handle := utl_file.fopen('LOG_DIR', file_name, 'a');
 if flag = '0' then
  utl_file.put_line(file_handle, '--------------------------------- START TIME :'|| s_time || ' ---------------------------------');
  utl_file.put_line(file_handle,'Delete table GAPS41.' || table_name || ' by column ' ||column_name || ' :');
  utl_file.put_line(file_handle, 'Totally ' || l_delete || ' records deleted!');
  utl_file.put_line(file_handle, 'The column min number: ' || min_data );
  utl_file.put_line(file_handle, '--------------------------------- FINISH TIME:' || e_time || ' ---------------------------------');
  utl_file.put_line(file_handle, '');
  utl_file.put_line(file_handle, '');
  utl_file.put_line(file_handle, '');
  utl_file.put_line(file_handle, '');
 else if flag = '1' then
  utl_file.put_line(file_handle, '--------------------------------- START TIME :'|| s_time || ' ---------------------------------');
  utl_file.put_line(file_handle,'');
  utl_file.put_line(file_handle,'Truncate table GAPS41.' || table_name || ' Complated !');
  utl_file.put_line(file_handle,'');
  utl_file.put_line(file_handle, '--------------------------------- FINISH TIME:' || e_time || ' ---------------------------------');
  utl_file.put_line(file_handle, '');
  utl_file.put_line(file_handle, '');
  utl_file.put_line(file_handle, '');
  utl_file.put_line(file_handle, '');
   end if;
  end if;
  utl_file.fflush(file_handle);
  utl_file.fclose(file_handle);
end;
/

三、创建批量删除的存储过程

CREATE OR REPLACE PROCEDURE GAPS41.DEL_TAB_BY_DATES(del_date    in varchar2,
                                                    tname       in varchar2,
                                                    column_name in varchar2,
                                                    date_format in varchar2,
                                                    commit_l    in number) AS
BEGIN
  DECLARE
    dd       varchar2(30);
    tn       varchar2(30);
    cn       varchar2(30);
	min_d 	 varchar2(300);
	m_d		 varchar2(20);
    dtf      varchar2(30);
    c_rowid  varchar2(300);
    cu_sql   clob;
    d_sql    clob;
    s_time   varchar2(30);
    e_time   varchar2(30);
    l_delete number := 0;
    type refcursor is ref cursor;
    c1 refcursor;
    C_COMMIT CONSTANT PLS_INTEGER := commit_l;
  BEGIN
    dd     := del_date;
    tn     := tname;
    cn     := column_name;
    dtf    := date_format;
    s_time := to_char(sysdate, 'yyyy-mm-dd hh24:mm:ss');
    cu_sql := 'SELECT ROWID ROW_ID FROM GAPS41.' || tn || ' where ' || cn || ' <to_char(sysdate-' || dd || ','''||dtf||''')';
    --dbms_output.put_line(cu_sql);
    open c1 for cu_sql;
  
    LOOP
      fetch c1
        into c_rowid;
      exit when c1%NOTFOUND;
      d_sql := 'DELETE FROM GAPS41.' || tn || ' WHERE ROWID = ''' ||c_rowid || '''';
      --dbms_output.put_line(d_sql);
      execute immediate d_sql;
    
      IF (MOD(C1%ROWCOUNT, C_COMMIT) = 0) THEN
      
        COMMIT;
        DBMS_LOCK.SLEEP(3);
      END IF;
    END LOOP;
    l_delete := l_delete + C1%ROWCOUNT;
    close c1;
    COMMIT;
	min_d :='select min('||cn ||') from GAPS41.'||tn;
	execute immediate min_d into m_d;
    e_time := to_char(sysdate, 'yyyy-mm-dd hh24:mm:ss');
    GAPS41.PUT_INTO_FILE(tn, l_delete,cn,m_d,s_time, e_time,0);
  END;
END;
/

四、创建执行删除的存储过程

CREATE OR REPLACE PROCEDURE GAPS41.DO_CLEAN(days  in varchar2,
                                            lines in number) is
BEGIN
  DECLARE
    d varchar2(20);
    l number;
    t clob;
    cursor t1 is
      select distinct table_name tn,
                      decode(table_name,
                             'IBPS_COMM_RECV',
                             'SENDDATE',
                             'IBPS_COMM_SEND',
                             'SENDDATE',
                             'IBPS_PAY_RELAT_REG',
                             'PLTDATE',
                             'IBPS_PAY_TRANS_REG',
                             'WKDT',
                             'IBPS_TXNET',
                             'TXNETGDT',
                             'UPSS_COREBKSERIAL',
                             'PLTDATE') cn
        from dba_tab_columns
       where TABLE_NAME IN ('IBPS_CHKBUSI_TMP_REG',
                            'IBPS_COMM_RECV',
                            'IBPS_COMM_SEND',
                            'IBPS_CORECHK_TEMP',
                            'IBPS_PAY_RELAT_REG',
                            'IBPS_PAY_TRANS_REG',
                            'IBPS_TXNET',
                            'UPSS_COREBKSERIAL')
         AND OWNER = 'GAPS41';
  BEGIN
    d := days;
    l := lines;
    for t_name in t1 loop
      if t_name.tn IN ('IBPS_CHKBUSI_TMP_REG', 'IBPS_CORECHK_TEMP') then
        t := 'truncate table GAPS41.' || t_name.tn;
        execute immediate t;
        --dbms_output.put_line(t);
        GAPS41.PUT_INTO_FILE(t_name.tn,
                             '0',
                             '',
							 '0',
                             to_char(sysdate, 'yyyy-mm-dd hh24:mm:ss'),
                             to_char(sysdate, 'yyyy-mm-dd hh24:mm:ss'),'1');
      else
        if t_name.tn in ('IBPS_PAY_TRANS_REG', 'IBPS_TXNET') then
          GAPS41.DEL_TAB_BY_DATES(d, t_name.tn, t_name.cn, 'yyyy-mm-dd', l);
        else
          GAPS41.DEL_TAB_BY_DATES(d, t_name.tn, t_name.cn, 'yyyymmdd', l);
        end if;
      end if;
    end loop;
  END;
END;
/

五、按清理协议清楚历史数据

5.1 调用现有的存储过程清楚数据

--使用DO_CLEAN
declare i number;
begin
for i in reverse 369 .. 360 loop
 GAPS41.DO_CLEAN(i,'10000');
end loop;
end;
/

--使用DEL_TAB_BY_DATES
exec GAPS41.DEL_TAB_BY_DATES(180,'IBPS_PAY_TRANS_REG','WKDT','yyyy-mm-dd','10000');

5.2 使用DBMS_PARALLEL清楚历史数据

CREATE OR REPLACE PROCEDURE Parallel_exec_delete(table_name in varchar2,
                         column_name in varchar2,
                         day_num  in number,
                         date_type in varchar2,
						 parallel_level in number,
						 chunk_size in number) is
begin
declare
  tn varchar2(50);
  cn varchar2(10);
  dn number;
  dtp varchar2(15);
  
  vc_task varchar2(100);
  vc_sql varchar2(2000);
  n_try number;
  n_status number;
  p_level number;
  ck_size number;
  
  file_handle utl_file.file_type;
  file_name   varchar2(50);
begin
  tn := table_name;
  cn := column_name;
  dn := day_num;
  dtp := date_type;
  p_level := parallel_level;
  ck_size := chunk_size;
  
  file_name   := to_char(sysdate, 'yyyymmdd') || '_parallel_delete.log';
  file_handle := utl_file.fopen('LOG_DIR', file_name, 'a');
  utl_file.put_line(file_handle,'Begin task =====================');
  utl_file.put_line(file_handle,'Table  : '||tn);
  utl_file.put_line(file_handle,'Column : '||cn);
  utl_file.put_line(file_handle,'Time   : '||to_char(sysdate, 'hh24:mi:dd'));
  utl_file.fflush(file_handle);
  --utl_file.fclose(file_handle);
  
  --Define the Task
  vc_task := tn||'_'||cn||'_DELETE';
  dbms_parallel_execute.create_task(task_name => vc_task); 

  --Define the Spilt
  dbms_parallel_execute.CREATE_CHUNKS_BY_ROWID(task_name => vc_task,
                                               table_owner => 'GAPS41',
                                               table_name => tn,
                                               by_row => true,
                                               chunk_size => ck_size); 
  
  vc_sql := 'delete /*+ ROWID(dda) */ from GAPS41.'||tn||' where rowid between :start_id and :end_id AND '||cn||' <to_char(sysdate-' || dn || ','''||dtp||''')';

  --Run the task
  dbms_parallel_execute.run_task(task_name => vc_task,
                                 sql_stmt => vc_sql,
                                 language_flag => dbms_sql.native,
                                 parallel_level => p_level);
  
  utl_file.put_line(file_handle,'===================== Task Finished  ' ||to_char(sysdate, 'hh24:mi:dd'));
  utl_file.put_line(file_handle,'');
  utl_file.fflush(file_handle);
  utl_file.fclose(file_handle);
  
  --Controller
  n_try := 0;
  n_status := dbms_parallel_execute.task_status(task_name => vc_task);
  while (n_try<2 and n_status != dbms_parallel_execute.FINISHED) loop
     dbms_parallel_execute.resume_task(task_name => vc_task);
     n_status := dbms_parallel_execute.task_status(task_name => vc_task);
  end loop;        

  --Deal with Result
  dbms_parallel_execute.drop_task(task_name => vc_task);                                   
end;
end;
/ 


--执行清除
begin
	Parallel_exec_delete('IBPS_PAY_TRANS_REG','WKDT',90,'yyyy-mm-dd',20,10000);
end;
/

查看parallel执行状态

select task_name, chunk_type, JOB_PREFIX from user_parallel_execute_tasks where task_name like '%_DELETE';
select status, count(*) from user_parallel_execute_chunks WHERE task_name like '%_DELETE' group by status;

select chunk_id, task_name, status, start_rowid, end_rowid from user_parallel_execute_chunks where rownum<10;

select sum(bytes)/1024/1024 from dba_undo_extents where status='ACTIVE';

select owner, job_name, JOB_ACTION, SCHEDULE_TYPE, state, last_start_date from dba_scheduler_jobs where job_name like 'TASK$_%';