本人使用的是oracle 7.3.4，其中有一个的数据特别大，有1000多万条数据，本人想将其中一部分按日期删除，但数据量特大，删除时报错误(本人删除方法为delete 表名 where 日期=1000000','3000');"
**to delete the records in the table "Foo", commit per 3000 records.
**
**/
(
p_TableName
in
varchar2,
-- The TableName which you want to delete from
p_Condition
in
varchar2,
-- Delete condition, such as "id>=100000"
p_Count
in
varchar2
-- 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;
/
I will do this:
1. backup table.
2. alter table nologgin;
3. delete from table(where clause).
As a DBAbe extremely careful when you say you want to delete data. I will reorganizae that table, parition the table, make part of data read only or make partition offline.There are many other ways to keep you dataandat the same time to run your query fast..
