SYMPTOMS
You are receiving the following error when using Auto Undo Management (AUM) while processing very large transactions.
ORA-01628: max # extents (32765) reached for rollback segment.
CAUSE
The customer encountering this problem was processing some very large transactions which were generating very large amounts of undo.
SOLUTION
It is not possible to increase the maxextents value in a locally managed undo tablespace.
-
Consider breaking the large transactions into smaller ones.
-
The Bug fix for 6499872 marks a segment as full so retries go to different segments in the Undo tablespace. Review Metalink Oracle Support to determine a patch is available for your release and platform.
@INTERNAL FIX DESCRIPTION:
@1. An undo segment grows in size and exceeds the maximum # of extents permitte
@d (ORA-1628 during add extent) at which point it is marked in-memory as "has-
@too-many-extents" (note that the undo tablespace may still have a lot of space
@ left in it).
@2. At this point the segment's status is "in-use" in the dictionary. However,
@no further transactions are permitted to bind to this segment. This is simila
@r to all slots being occupied in the transaction table.
@3. Subsequently a shrink may free some of the extents of the undo segment of i
@nterest. Once the extent count falls below a low-watermark, the undo segment q
@ualifies for further binds.
@4. While onlining undo segments during db startup, the number of extents in th
@e undo segment are checked and if above the low water mark, the segment will b
@e marked with "has-too-many-extents" there by preventing binds to this undo se
@gment until it is shrunk and the number of extents fall to or below the low wa
@ter mark.
This issue is fixed in
11.1.0.7 (Server Patch Set)
10.2.0.4 (Server Patch Set)
- Use the underscore parameter _rollback_segment_count to control how many Undo segments stay online over time. This will help balance the work over more segments.
NOTE: SMON will ignore the setting for this parameter in many cases and a fix is available on various release levels through bugs 6320407 (for 9.2.x releases), 7527908 (for 10g releases), or 6658921 (for 11g releases).
