刪除Oracle大量資料，又不影響使用者操作
筆記：
常常遇到這種要求清某段區間的資料，但是又不能影響到使用者線上操作。(雖然這種狀況通常是使用者造成)。
由於一開始table規劃不當，且沒有買partition的授權，又不可能停機來進行table轉為partition的動作，所以只能慢慢的刪資料。
再加上DB Server本身的硬體很普通，隨便刪個資料都會影響使用者的操作，故需在使用者的工作階段不是Active時，再進行刪除。
最後執行方式如下：
常常遇到這種要求清某段區間的資料，但是又不能影響到使用者線上操作。(雖然這種狀況通常是使用者造成)。
由於一開始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;
----------------------------------------------
留言