Re: Fetching across commit or rowid???

From: keith boulton <boulke_at_globalnet.co.uk>
Date: Sat, 27 Mar 1999 15:36:11 GMT
Message-ID: <36fcf049.17733759@195.147.246.90>

On Fri, 26 Mar 1999 07:40:40 GMT, "Daren Jackson" <daren_at_webdiego.com> wrote:

>We are having a problem with a stored procedure that deletes about 4 million
>rows from 4 different tables. We are receiving ORA-015555 Snapshot too
>old....rbs segment (02) too small. The SQL in the procedure does do fetches
>across commits and we are pretty sure this is the problem. What we can't
>decide is the better way to fix this. I want to close the cursor before
>each commit (500 rows) and my fellow DBA wants to sort by rowid to ensure
>block integrity in the delete. What do you all think???
>

Personally, I would (optimally) explicitly assign the transaction to a large rollback segment and remove the commits - this would avoid the problem occurring because of the procedure overwriting its own rollback information. Of course this could requre a very large rollback segment, and does to avoid the problem occuring because of other transactions.

In any case, I would not re-open the cursor at each commit, but rather re-open the cursor when the error occurs. Received on Sat Mar 27 1999 - 09:36:11 CST