oracle大数据量分批删除

很多时候，我们要清理一个大表的数据，比如几个亿，业务还不能停，还只能delete，这种情况下分批删除是个好办法。

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;      DBMS_OUTPUT.PUT_LINE(n_delete);    end if;    commit;  end loop;  commit;  DBMS_OUTPUT.PUT_LINE('Finished!');  DBMS_OUTPUT.PUT_LINE('Totally ' || to_char(n_delete) || ' records deleted!');end;

执行方法：exec delBigTab('XXXXX','status=1','10000');

清理表之后，重新收集下该表的统计信息

EXECUTE dbms_stats.gather_table_stats (ownname=>'XXXX', tabname=>'XXXXXX',estimate_percent=>30,cascade=>true,degree => 4);