Re: delete and rollback segs

nigel_at_cnw01.storesys.coles.oz.au (Nigel Harwood) asks:
>We are developing an application which does some large deletes on
>tables and are finding that our roll back segments are overflowing.
>
>The simple answer is that we should increase the size of these
>segments to ensure they are large enough for our largest delete.
>
>Unfortunately we don't have enough disk space for that.
>
>Another suggestion was that we have just one big RBS rather than
>five smaller ones. The DBA says this is not a good idea as this
>causes contention.
>
>Anyone have any other ways that they have gotten around this?

cooper_at_beno.CSS.GOV (Dale Cooper) suggested:

>First of all, how about breaking up the deletes into chunks. For example,
>if your table contains a date field, perform deletes based on a time window
>instead of using one command to cover all dates needing to be deleted.

Better yet, you can use the psuedo column ROWNUM (refer to the SQL Language Reference Manual, pg. 3-26)

e.g.

	delete from EMP
	where ( your where clause here )
	and rownum < 1001;

	commit;

This will delete up to 1000 rows from table EMP with your where clause. Wrap it around a PL/SQL block / Pro*Whatever loop and you will have an effective delete mechanism. Of course the number 1000 is arbitrary. :)

--
 -----------------------------------------------------------------------------
 Alvin W. Law           Applications Engineer, Manufacturing Applications
 alaw_at_us.oracle.com             Voice : (415) 506-3390  Fax : (415) 506-7262
                What else do you want from a 4-line .signature?
 --------------------------------------------------------------------------^\
Segmentation fault (core dumped)