In that sort of situation -- assuming that the CTAS / drop / rename
suggestion can't be used -- I generally drop to PL/SQL bulk operations.
declare
type rowid_type is table of rowid index by pls_integer;
v_rowids rowid_type;
c_batchsize constant number := 1000000;
cursor csr is
select rowid from big_table
where ...;
begin
open csr;
loop
fetch csr
bulk collect into v_rowids
limit c_batchsize;
exit when v_rowids.COUNT = 0;
forall c in 1..v_rowids.COUNT
delete from big_table
where rowid = v_rowids(c);
commit;
end loop;
close csr;
end;
/
