oracle数据库批量删除,批量删除Oracle数据库的数据

在使用delete语句删除数据时，数据库是要做日志记录的，以便将来可以恢复数据，可是我在删除上百万条数据时，十分缓慢甚至死机，请问有没有什么好方法?[@more@]

网友观点一:

create or replace procedure 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

SELECT COUNT(*) INTO CNT FROM ep_arrearage_bak WHERE TO_CHAR(DF_DATE,'MM')='01';

FOR I IN 1..TRUNC(CNT/500)+1 LOOP

DELETE 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')

你看看执行结果我试验过，效果还可以