一次删除数据太多,会占用大量IO,严重影响数据库读写性能从而影响应用。
通过存储过程1000条批量清楚,降低删除操作对数据库的影响。
删除语句,注意修改表名和where条件。
declare
cursor mycursor is select ROWID from 表名 WHERE createddate < to_date('2021-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
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 1000;
exit when v_rowid.count=0;
forall i in v_rowid.first..v_rowid.last
delete from 表名 where rowid=v_rowid(i);
commit;
end loop;
close mycursor;
END;
/