Re: Running out of rollback

If you have PL/SQL, you could do your cursor and then have counter count rows deleted. Then for every 500 rows, do a commit thus releasing the rollback segment. Don't forget to reset the counter back to zero before continuing the next count of 500 rows.

John Blackburn (jb2_at_qdot.qld.gov.au) wrote:
: Gary Nilges (gnilges_at_tyrell.net) wrote:
: > I'm trying to delete about 7 million rows from a 12 million row ORA 7.1
: > database with a Pro*C program. I open a cursor selecting all the rows that
: > meet my age criteria (older then 90 days). I then spin through my cursor rows
: > and do a 'delete where current of'.
: >
: > My problem is this. I keep running out of rollback space in the declare cursor
: > statement. Below is the text of my statement and the error. My rollback
: > segment grows to about 300 meg before bombing.
: >
: > I was told that under certain circumstances declare cursor and select
: > statements cause rollback activity, though I really didn't expect it in this
: > case. Any ideas how this application can be changed to avoid rollback
: > activity? Any help is appreciated.
: >
 

: In my experience, deleting rows causes rollback activity for the simple
: reason that you might need to rollback your transaction thus 'undeleting'
: all your rows that you just deleted.
 

: We had a problem similar to yours on our system. We overcame the problem
: by having nested cursors that deleted a sufficiently small number of rows
: at a time so as not to blow out the roll back segment, and performing
: a commit between each group of rows deleted.
 

: I hope this helps,
 

: --
:
: John Blackburn Phone: +61 7 2534634
: jb2_at_qdot.qld.gov.au Fax: +61 7 8541194
Received on Mon Mar 13 1995 - 23:23:48 CET