Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Fetching across commit or rowid???
Daren Jackson wrote in message ...
>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???
Your fellow DBA's solution is rather neat - but it won't (always) work. The rowid represents the block the row is originally placed in. If the row grows, it could migrate to another block, in which case the rowid no longer represents the block in which it is now located, so you could still get "snapshot too old" in this situation ( but it might be very rare ).
Only your solution, of closing the cursor after each commit, is 100% robust.
Dave.
--
Remove "nospam" from my address to reply by email
Received on Fri Mar 26 1999 - 23:54:58 CST