原文链接:https://www.iteye.com/blog/eeeewwwqq-1976568
大致就是先写一个通用的存储过程用来循环删除表数据
代码如下
create or replace procedure delBigTab(p_TableName in varchar2,
p_Condition in varchar2,
p_Count in varchar2) 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;
三个变量:表名、删除的条件、每次删除的条数
例子:
1.删除表table_name的所有行,每次删除1000条
使用该存储过程方法:call delBigTab('table_name','1=1','1000');
2.删除表table_name中字段a=1的所有行,每次删除500
使用该存储过程方法:call delBigTab('table_name','a=1','1000');
很有用的存储过程,分享一下,有更好的希望朋友们多多分享下。
追加一个脚本,同样是批量删除
declare
cursor mycursor is
SELECT ROWID FROM T_PA_0829 t order by t.alarmtime;
type rowid_table_type is table of rowid index by pls_integer;
v_rowid rowid_table_type;
BEGIN
open mycursor;
loop
fetch mycursor bulk collect
into v_rowid limit 5000;
exit when v_rowid.count = 0;
forall i in v_rowid.first .. v_rowid.last
delete from T_PA_0829 where rowid = v_rowid(i);
commit;
end loop;
close mycursor;
END;
增加等待一秒的方式,防止数据库卡死
--等待一秒
dbms_lock.sleep(1);
用管理员帐号赋权限
--赋权操作
GRANT EXECUTE ON SYS.DBMS_LOCK TO user;