搜尋此網誌

2014/12/25

刪除Oracle大量資料,又不影響使用者操作

筆記:

常常遇到這種要求清某段區間的資料,但是又不能影響到使用者線上操作。(雖然這種狀況通常是使用者造成)。

由於一開始table規劃不當,且沒有買partition的授權,又不可能停機來進行table轉為partition的動作,所以只能慢慢的刪資料。

再加上DB Server本身的硬體很普通,隨便刪個資料都會影響使用者的操作,故需在使用者的工作階段不是Active時,再進行刪除。


最後執行方式如下:

  • 先檢查是否在可執行刪除的時段 (batch、backup及使用者大量使用的時間先避開)
  • 每次刪除的資料區間不可太大,最好10~15秒內可跑完一段
  • 每次刪除前先檢查使用都是否在使用中,如果是的話,就等待

以下為Sample PL/SQL block

----------------------------------------------
--此段是因應本次需求開的table,不一定每次都要
create global temporary table data_work_area
        (rid rowid)
      on commit delete rows;
----------------------------------------------

declare
  sti number := 66; -- 
  sti_max number := 66+23; -- 
  sti_inc number := 0.0003125; -- 1/3200日, 27秒區間為一段
  i  number := 0;
  i1 number := 0;

  start_dt timestamp;

  current_d number;
  d_start1 number := 0.385416666666667; -- 9:15 AM
  d_end1 number := 0.697916666666667; -- 16:45 PM
  d_start2 number := 0.21875; -- 5:15
  d_end2 number := 0.291666666666667; -- 7:00 AM
  d_start3 number := 0.770833333333333; -- 18:30 PM
  d_end3 number := 0.90625; --21:45 PM

  can_run boolean; -- 可進行刪除
  can_query boolean; -- 沒有使用者段段active中
  prev_stat boolean;
  active_sessions number;


begin
  --發送mail
  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''192.168.0.200''';
  UTL_MAIL.send(sender => 'batch-notify@mycompany.com',
            recipients => 'yourname@mycompany.com',
               subject => 'Purge Job started',
               message => 'Job started',
             mime_type => 'text; charset=us-ascii');
  --

  select trunc(systimestamp, 'DD')
    into start_dt
  from dual;

  dbms_application_info.set_action('Starting');
  
  --取得要刪除的時間

  select trunc(sysdate) - to_date('20140916' , 'YYYYMMDD')
    into sti
  from dual;

  select trunc(sysdate) - to_date('20140901' , 'YYYYMMDD')
    into sti_max
  from dual;

  --刪除作業開始程式段
  while sti <= sti_max loop

    can_run := false;
    while can_run = false loop
      select sysdate - trunc(sysdate) into current_d from dual;
      if ((current_d >= d_start1) and (current_d <= d_end1)) or ((current_d >= d_start3) and (current_d <= d_end3)) or ((current_d >= d_start2) and (current_d <= d_end2)) then
        prev_stat := can_run;
        can_run := true;
      else
        prev_stat := can_run;
        can_run := false;
        dbms_application_info.set_action('Sleeping');
        dbms_lock.sleep(120);
      end if;
    end loop;
    if can_run then
      can_query := false;
      while can_query = false loop -- waiting for dumb sessions to terminate
        -- check active session

         select count(1) into active_sessions
           from v$session
         where (program in ('sqlplus.exe',
                            'plsqldev.exe') and status = 'ACTIVE')
            or (program like 'rman@%')
            or (module = 'DBMS_SCHEDULER');


        if active_sessions > 0 then
          dbms_application_info.set_action('Running: Waiting active sessions');
          dbms_lock.sleep(30);
        else
          can_query := true;
        end if;
        -- check active session end
      end loop;
      --
      dbms_application_info.set_action('Running: insert rowid to temp');
      
      --資料刪除段SQL,這邊需自行修改
      --需求:每次執行的時間不可太長
      insert into data_work_area
        (SELECT MAX(b.ROWID)
           FROM scott.DATA b
          where b.atime between (start_dt - (sti + sti_inc)) and
                ((start_dt) - sti)
          GROUP BY b.ID, b.time_str, b.NAME);
      i := sql%rowcount;

      dbms_application_info.set_action('Running: deleting');

      delete FROM scott.DATA a
       WHERE a.atime between (start_dt - (sti + sti_inc)) and
             (start_dt - sti)
         and a.ROWID NOT IN (SELECT * from data_work_area);
      i1 := sql%rowcount;

      dbms_application_info.set_action('Running: clean up');
      
      insert into PURGE_LOG
      values
        (systimestamp, i, i1, start_dt - (sti + sti_inc), start_dt - sti);

      commit;
      --rollback;

      sti := sti + sti_inc;
    end if;
  end loop;
  UTL_MAIL.send(sender => 'batch-notify@mycompany.com',
            recipients => 'yourname@mycompany.com',
               subject => 'Purge Job completed',
               message => 'Job completed',
             mime_type => 'text; charset=us-ascii');
  dbms_application_info.set_action('completed');
end;
/
--此段是因應本次需求開的table,不一定每次都要
drop table data_work_area;
----------------------------------------------

沒有留言: