Re: Rollback Segment Trouble

From: George Tetterton <georget_at_pooh.aadt.com>
Date: Tue, 17 May 1994 18:35:45 GMT
Message-ID: <GEORGET.94May17133546_at_pooh.aadt.com>

>>>>> "Carmen" == Carmen Iannacone <carmen_at_verdi.iisd.sra.com> writes:

    Carmen> Hi,

    Carmen> 	I've gotten the following errors during large deletes in
    Carmen> SQL*Plus.  I've checked the Server Codes and Messages manual,
    Carmen> but I'm still somewhat unsure as to how to proceed.

    Carmen> ORA-01562: failed to extend rollback segment (id = 1)

    Carmen> ORA-01628: max # extents (121) reached for rollback segment     Carmen> MY_ROLLBACK

    Carmen> 	Notably, why is "max # extents" 121?  Should I be doing
    Carmen> some house keeping to prevent this?  I haven't gotten through
    Carmen> the FAQ, so if you could direct me to a portion of it which is
    Carmen> applicable, I'd appreciate it.

If you read the errors manual it says:

Action: If possible, increase the value of either MAXEXTENTS or PCTINCREASE initialization parameters.

On the Unix machines I have worked with (SUN, AIX, Mips) 121 is the maximum number of Extents you may have for one object or transaction, therefore increasing MAXEXTENTS is not the answer. At this point you have two ways to fix the problem 1) increase the PCTINCREASE value in storage clause of the rollback tablespace or 2) set a higher INITIAL value in the storage clause of the rollback tablespace. The latter will require you to drop the table space and recreate it. The PCTINCREASE change will take effect the next time a transaction is run. Warning: there is a point where larger extents will hurt performance so don't carried away with your INITIAL value.

You could, as was mentioned in some other posts, do internal commits. This is a good answer if it can be done. But, this will make rollback of the entire transaction impossible.

If you have access to Oracle's RTSS (Real Time Support System) there were some articles about this problem for version 6 database (refer to: RTSS Doc #100597.267). This is a very old document, I am sure there have been updates for V7 by now.

    Carmen> Thanks alot!

    Carmen> Carmen (carmen_at_sra.com)

    Carmen> --------------------------------------------------------------------------

    Carmen> Carmen Iannacone (703) 558-7820 SRA Corporation 2000 15th     Carmen> Street North Office 1055 Arlington, Va. 22201-2640

Thanks,

George E. Tetterton
Sabre Decision Technologies

Email: 		georget_at_aadt.com
Phone: 		(817)-931-1906
Office: 	2S4C-54

--
Thanks,

George E. Tetterton
Sabre Decision Technologies

Email: 		georget_at_aadt.com
Phone: 		(817)-931-1906
Office: 	2S4C-54

Received on Tue May 17 1994 - 20:35:45 CEST