I'm sorry, I forgot to include a COMMIT into my sample code. It should really be
DELETE FROM test WHERE rownum <= 50 AND ....;
EXIT WHEN SQL%ROWCOUNT = 0;
No need for another outer loop as in your last post, as all the deletes are taken care of by the inner loop. Your outer loop would only cause ceil(cnt/10000) number of full table scans after all the rows have allready been deleted.
Now if you really have problems with deleted even 50 rows at a time then something is *very wrong* with your rollback segments settings. BTW, what is the avarage row size in the table TEST? What I think is happening is some of your rollback segments have extended to a very large size occupying all of the available space in your RB tablespace, while others are very (extremely?) small, like your RB #3, and are unable to extend. Probably you should shrink the big rollback segments to a normal size and also set the OPTIMAL parameter for all your RB segments. Or maybe create a special large rollback segment and assigne it to your transaction before each delete in your loop.
[Edited by jmodic on 06-08-2001 at 04:08 PM]
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?