Delete Records for a huge table ( rollback error)
there is a huge database , the data volume is 450GB, the largest table contains 60million records and the record size is 1K ~1.5K
(no lob column)
now trying to delete 500K records in this table (based on 2 indexed columns)
the platform is RS6000 s7A , 2GB memeber , CPU 4*262MHZ , Oracle8.1.5
I notice that the deletion is using one rollback segment and it grows to 10GB and failed to allocate next extent . How to solve this problem .
the example of code for deletion is :
#!/bin/sh
svrmgrl << EOF
connect username/password;
alter table tb_name nologging;
declare
v_id tb_name.id%type;
v_counter number:=0;
v_total number :=0;
cursor V_Cur is
select rkxh from tb_name where f_a=xxx and f_b=yyy;
begin
open V_Cur;
loop
fetch V_Cur into v_id;
exit when V_Cur%notfound;
v_counter:= v_counter+1;
delete from tb_name where id=v_id;
v_total:= v_total + 1;
if v_counter>=10000 then --commit for eevery 1000
commit;
v_counter := 0;
end if;
end loop;
commit;
close V_Cur;
exception
when others then
rollback;
close V_Cur;
end;
/
alter session disable parallel dml;
alter table tb_name logging;
disconnect
exit
EOF
question1 : It seems Oracle put not only the deleted data blocks in rolllback (otherwise how it is possible that the rollback is used up to 10GB !)
question2: in this case , how to optimize the deletion function ?
Re: Delete Records for a huge table ( rollback error)
Quote:
Originally posted by ligang
the example of code for deletion is :
#!/bin/sh
svrmgrl << EOF
connect username/password;
alter table tb_name nologging;
Nologging will help only in certain cases like creating/rebuilding index. Delete info will be logged in the redo.
Secondly since you are committing inside a cursor, this should span several rollback segs and not just one, unless all your other RBS are full.
Slimdave...good input.