Oracle資料庫Delete刪除千萬以上普通堆表資料的方法

Oracle資料庫Delete刪除千萬以上普通堆表資料的方法

需求：Oracle資料庫delete刪除普通堆表千萬條記錄。

直接刪除的影響：

1.可能由於undo資料表空間不足從而導致最終刪除失敗的問題；

2.可能導致undo資料表空間過度使用，影響到其他使用者正常操作。

改進方案：每刪除1k行就提交一次。（這樣就把一個大事物拆分成了若干個小事物）

注意：下面方法以刪除2014年之前的所有記錄為例，請根據你的實際情況修改，防止誤操作。

方法1

declare
    cursor [del_cursor] is select a.*, a.rowid row_id from [table_name] a order by a.rowid;
begin
    for v_cusor in [del_cursor] loop
          if v_cusor.[time_stamp] < to_date('2014-01-01','yyyy-mm-dd') then
              delete from [table_name] where rowid = v_cusor.row_id;
          end if;
          if mod([del_cursor]%rowcount,1000)=0 then
              commit;
          end if;
    end loop;
    commit;
end;

方法1中變數說明：

[del_cursor] 遊標名

[table_name] 你要刪除資料的表名

[time_stamp] 你用作過濾條件的表的時間欄位名稱

方法2

declare 
maxrows number default 1000;
delete_ct number default 0;
begin
select count(1)/maxrows  into delete_ct from [table_name] where [time_stamp] < to_date('2014-01-01','yyyy-mm-dd');
for i in 1..TRUNC(delete_ct)+1
loop
delete [table_name] where [time_stamp] < to_date('2014-01-01','yyyy-mm-dd') and rownum <= maxrows;
commit;
end loop ;
end;

方法2中變數說明：

[table_name] 你要刪除資料的表名

[time_stamp] 你用作過濾條件的表的時間欄位名稱

Note

兩種方法的核心的思路都是把一個大事物拆分成了若干個小事物，無論採用哪種方法，都建議先在對應的測試環境中測試後再考慮是否可以在實際生產使用。

順便說一句，這樣的大表應該要綜合考慮下是否可以改造成分區表。