Re: Help! - Rollback Segment Problem

jstrickland_at_dbmsmail.dsac.dla.mil (James Strickland) writes:

>RE: Blowing rollback segments when updating a large database.
>
 

>Since the program was blowing with rollback segment errors, the
>DBA created an extremely large rollback segment. I specified this
>large rollback segment in a SET TRANSACTION which is placed
>immediately before the SELECT statement. The problem is that if
>I try to COMMIT any of my database updates inside of the SELECT
>while loop, the selected rollback segment is released I am right
>back where I started. The process is likely to blow on the next
>FETCH statement. If I commit ONLY at the very end of the entire
>SELECT while loop, the rollback segment must be prohibitively large.
>
>Soon we are to move to an even larger set of input files and we
>are in a quandry. If anyone has any tips, insights, or helpful
>thoughts, they would be greatly appreciated.
>

Put the set transaction statement immediately after the commit, and prior to any other SQL statements. After a commit, the round-robin algorithm is re-applied to determine your next rollback segment. If the first operation after commit is to re-assign a specific RBS, you will be home free. Also, for efficiency sake, use the cursor with the WHERE CURRENT OF clause.

Darin Brown
Oracle Commercial Consulting Received on Sat Nov 05 1994 - 06:59:17 CET