案例讨论：批量删除Oracle数据库的数据

在使用delete语句删除数据时，数据库是要做日志记录的，以便将来可以恢复数据，可是我在删除上百万条数据时，十分缓慢甚至死机，请问有没有什么好方法? 网友观点一:create or replace procedure delete_tableisi 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条数据提交一次。DECLARECNT NUMBER(10):=0;I NUMBER(10);BEGINSELECT COUNT(*) INTO CNT FROM ep_arrearage_bak WHERE TO_CHAR(DF_DATE,'MM')='01';FOR I IN 1..TRUNC(CNT/500)+1 LOOPDELETE FROM ep_arrearage_bak WHERE TO_CHAR(DF_DATE,'MM')='01' AND ROWNUM<=500;COMMIT;END LOOP;END;专家意见:几个办法:1. 假如删除的数据是大部分,建议使用楼上的方法把要保留的数据放在一个临时表里,truncate table后再放回来2. 也可以分段提交,楼上也提到了3. 专门使用一个大回滚段4. 假如确认将来不需要做恢复,改为非归档模式,删除完改回来再做个备份.专家给出的解决方案:有条件的分步删除数据表中的记录--创建测试表create table test as select * from dba_objects;Table created.--创建删除表的存储过程create or replace procedure deleteTab--插入语句 SQL> insert into test select * from dba_objects;6374 rows created.SQL> /6374 rows created.SQL> /6374 rows created.SQL> commit;--创建删除的存储过程create or replace procedure 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 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; /--执行语句SQL> exec deleteTab('TEST','object_id >0','10000')你看看执行结果我试验过，效果还可以