Re: Fail to extend rollback segment ?

wshiang_at_a.cs.okstate.edu (WANG SHIANG HUEY) wrote:

>Hi,
>when I insert 200,000 records into a table from a query, I encounter
>the following errors.
 

>ORA-01562: failed to extend rollback segment (id = 1)
>ORA-01628: max # extents (121) reached for rollback segment RB_TEMP
 

>I have added a datafile size 20M (total is 35M)
>and increase the initial to 1M and next to 500K on ROLLBACK_DATA
>tablespace, but it didn't solve the
>problem. The number of extents is limited by db_block_size, so max
>I can have is 121. I may be thinking in a wrong direction.
>Please advise, thanks !
>--
>Shiang-Huey Wang
>Leather Center, Inc.
>sherry_at_leathercenter.com
>wshiang_at_a.cs.okstate.edu

You have two options (that I know of):

1. Break you transaction down into smaller units so you commit, and release rollback segments, more often.
2. Create a rollback segment with very large extents and make sure your long-running transaction uses the right one by issuing a SET TRANSACTION USE ROLLBACK SEGMENT statement.

I personally have found option 1 easier to implement than 2.

HTH

--
Mark Wagoner
mwagoner_at_iac.net