批量清除一個表的數(shù)據(jù)，TB級別數(shù)據(jù)。

有個需求，要求清理8TB的數(shù)據(jù)，只保留一個月的數(shù)據(jù)，現(xiàn)有數(shù)據(jù)量由2010年至2018年底都需要清除，因此寫了通用的該清除腳本。該表為沒有分區(qū)的含有BLOB的大表，一共由三列，file_no,BLOB,還有create_time列，其中file_no為主鍵，create_time非空。

腳本如下：
create or replace procedure proc_batch_delete(PI_table_name in varchar2, -- table name which will delete data
PI_where_condition in varchar2, -- delete sql condition
PI_thread_count in varchar2, -- thread number of the sql which use parallel hint
PI_commit_count in varchar2) is -- per delete count and commit count
v_delete integer;
v_sql varchar2(2000);
begin
dbms_output.put_line('Delete table is ' || PI_table_name || ';');
dbms_output.put_line('Where condition is ' || PI_where_condition || ';');
dbms_output.put_line('Number of threads is ' || to_char(PI_thread_count) || ';');
dbms_output.put_line('Commit count is ' || to_char(PI_commit_count) || ';');
dbms_output.put_line('Now start to batch delete. Timestamp is ' || to_char(sysdate, 'yyyy-mm-dd hh34:mi:ss'));

v_delete := 0;

execute immediate 'alter session enable parallel dml';

v_sql := 'delete /+ parallel('||PI_table_name || ' ' || PI_thread_count ||') / from '
|| PI_table_name ||' where '|| PI_where_condition || 'and rownum <= ' || PI_commit_count;
dbms_output.put_line('Sql is ' || v_sql);

while 1=1 loop
EXECUTE IMMEDIATE v_sql;
if SQL%NOTFOUND then
exit;
else
v_delete:=v_delete + SQL%ROWCOUNT;
end if;
commit;
dbms_output.put_line('already deleted :' || to_char(v_delete) ||'.Timestamp is' || to_char(sysdate, 'yyyy-mm-dd hh34:mi:ss'));
end loop;
commit;

dbms_output.put_line('End to batch delete. Timestamp is ' || to_char(sysdate, 'yyyy-mm-dd hh34:mi:ss'));

end proc_batch_delete;

1.該圖

2.執(zhí)行語句

3.輸入值

4.顯示值
pi_table_name 需要刪除數(shù)據(jù)的表名
pi_where_condition 選擇條件
pi_thread_count并發(fā)線程數(shù)
pi_commit_count 每次刪除和提交事務(wù)數(shù)量