pl/sql to delete from a large table

[navink123]

I have a large table from which I need to delete all the data and reload it again. I do not have a truncate table right on this table.
When I try to do a delete on the table, I run into rollback space errors. Somebody suggested me to use a pl/sql and do frequent commits.
I have not used pl/sql much before.
Can somebody please tell me what will be the pl/sql to achieve this.
Thanks,
N

 

[Dagon]

Haven't checked this, but you could do something like:

begin
loop
delete from table where rownum <= 10000; -- or some other value
if sql%rowcount = 0 then exit;
end loop;
end;

 

[navink123]

Thank You Dagon,
But where are we doing the commit in this script.
Or does it automatically commit with every loop.
Thanks,
N

 

[Dagon]

Sorry, forgot to put that in:

begin
loop
delete from table where rownum <= 10000; -- or some other value
commit;
if sql%rowcount = 0 then exit; end if;
end loop;
end;

 

[SantaMufasa]

Navink and Dagon,

Now you will by &quot;hyper-committing&quot; after each deleted row...Just move the COMMIT down to immediately follow the END LOOP; then it will commit after each 10K rows.

Dave
Sandy, Utah, USA @ 17:53 GMT, 10:53 Mountain Time

 

[Dagon]

I don't see how I can be committing after every row when the delete actually does 10000 rows at a time. I agree the commit should be at the end to make sure the sql%rowcount is referring to the delete statement rather than the commit, though.

 

[navink123]

SantaMufasa,
I agree with Dagon. Aren't we committing after every 10000 deletes at a time.

I am still confused where the commit is going to be.

Can you please explain, if what you said is true, how is it so.

Thanks,
N.

 

[Dagon]

The commit should definitely be in the loop. My only point was that you might not want it between the &quot;delete&quot; and the sql%rowcount in case the commit itself (being a SQL statement) resets sql%rowcount. I'm not sure if it does or not but it's probably good practice to keep the sql%rowcount immediately after the statement it is meant to refer to. I would suggest:

begin
loop
delete from table where rownum <= 10000; -- or some other value
if sql%rowcount = 0 then exit; end if;
commit;
end loop;
commit;
end;

 

[SantaMufasa]

Ohhh Myyyy Gosh ! I must have been smoking &quot;cyber-dope&quot;. Sorry...Mentally, I just put the &quot;<= 10000&quot; up on the LOOP condition rather than the DELETE condition. My bad; my apologies. (Will you forgive me just this once?) I need a vacation.

Dave
Sandy, Utah, USA @ 16:58 GMT, 09:58 Mountain Time

 

[Dagon]

The answer appears to be that commit doesn't reset sql%rowcount, so the original SQL works OK. However, I still think it's a bad idea to separate the sql%rowcount from the statement which gave rise to it. If you've got 5 or 6 lines of code between the two, then another programmer is liable not to realise that they are connected and put another SQL statement in between the two, thereby ruining the logic.