Re: Running out of rollback

In article <D56vMn.788_at_tyrell.net>, 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.

Any insert/update/delete statement causes rollback activity to allow undoing the transaction. You cannot avoid the use of rollback segments, though you can minimize its usage by having intermittent commits. Consider the following code fragment:

done = FALSE;

while (!done) {

    EXEC SQL

        DELETE FROM my_table
        WHERE  creation_date < (sysdate - 90)
        AND    rownum <= 5000;

    if (sqlca.sqlcode == -1403)
       done = TRUE;

    EXEC SQL COMMIT;
}

The above code fragment deletes/commits 5000 rows at a time. The number 5000 can be any number, even a host variable.

Good luck.

--
"And this is all I have to say about that..."   - F. Gump
      ___
     (o o)
+-oo0-\_/-0oo---------------------------------------------------------------+
|  Alvin W. Law ..... Oracle Corporation ....... Email: alaw_at_us.oracle.com  |
+---------------------------------------------------------------------------+

ORA-03113: end-of-file on communication channel