oracle删除大数据时会对undo造成很大压力,死锁也会经常产生,所以经常采取分批提交的方式。
下面的存储过程转自:http://www.eygle.com/archives/2005/02/ecioioaeoeeeioe.html
create or replace procedure deleteTab
(
p_TableName in varchar2, -- The TableName which you want to delete from
p_Condition in varchar2 default '1=1', -- Delete condition, such as "id>=100000"
p_Count in varchar2 default '10000' -- Commit after delete How many records
)
as
pragma autonomous_transaction;
n_delete number:=0;
begin
while 1=1 loop
EXECUTE IMMEDIATE
'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'
USING p_Count;
if SQL%NOTFOUND then
exit;
else
n_delete:=n_delete + SQL%ROWCOUNT;
end if;
commit;
end loop;
commit;
DBMS_OUTPUT.PUT_LINE('Finished!');
DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');
end;
/
execute trswcmnew.deletetab('T1','id>20','2000');