createorreplaceprocedure delete_table is i number(10); begin for x in (select * from emp where DEPTNO like ‘a%’) loop delete emp where emp.id = x.id i:=i+1; if i>1000 then commit; i:=0; end if; end loop; exception when others then dbms_out.put_line(sqlcode); rollback; end delete_table;
或者
–每500条数据提交一次 DECLARE CNT NUMBER(10):=0; I NUMBER(10); BEGIN SELECTCOUNT(*) INTO CNT FROM ep_arrearage_bak WHERE TO_CHAR(DF_DATE,‘MM’)=’01′; FOR I IN 1..TRUNC(CNT/500)+1 LOOP DELETEFROM ep_arrearage_bak WHERE TO_CHAR(DF_DATE,‘MM’)=’01′ ANDROWNUM<=500; COMMIT; END LOOP; END;
2、把要保留的数据放在一个临时表里,truncate table原表后再放回来;
createtable t_back asselect * from t where …. droptable t; rename t_back to t;
3. 专门使用一个大回滚段 ,比如定义:undo tablespace 2G
4、如果将方法1做一点修改,可以这么做:
有条件的分步删除数据表中的记录
–创建测试表 createtable test asselect * from dba_objects;
–创建删除表的存储过程 createorreplaceprocedure deleteTab
–插入语句 SQL> insertinto test select * from dba_objects; 6374 rows created. SQL> / 6374 rows created. SQL> / 6374 rows created. SQL> commit; – 创建删除的存储过程 createorreplaceprocedure deleteTab /** ** Usage: run the script to create the proc deleteTab ** in SQL*PLUS, type "exec deleteTab(‘Foo’,'ID>=1000000′,’3000′);" ** to delete the records in the table "Foo", commit per 3000 records. ** Condition with default value ’1=1′ and default Commit batch is 10000. **/ (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 EXECUTEIMMEDIATE ‘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; /