Home -> Usenet -> comp.databases.oracle -> Re: Running out of rollback

Re: Running out of rollback

From: John Blackburn <jb2_at_qdot.qld.gov.au>
Date: 13 Mar 1995 01:30:13 GMT
Message-ID: <3k0775$20d_at_camelot.qdot.qld.gov.au> 







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 - 02:30:13 CET












Original text of this message