ORA-1628: max # extents 32765 reached for rollback segment _SYSSMU6_621766836$

One DB suddenly got many blow errors in a period of time:
Wed Mar 28 23:53:58 2012
ORA-1628: max # extents  32765 reached for rollback segment _SYSSMU6_621766836$
Wed Mar 28 23:53:58 2012
ORA-1628: max # extents  32765 reached for rollback segment _SYSSMU7_1070497209$

This error means an undo segment reached its max extents so that it couldn't extend any more：
SQL> select SEGMENT_NAME,MAX_EXTENTS from DBA_ROLLBACK_SEGS;
SEGMENT_NAME                   MAX_EXTENTS
------------------------------ -----------
_SYSSMU59_1810053014$                32765
_SYSSMU58_3572456133$                32765
_SYSSMU57_2783840325$                32765
_SYSSMU56_2339751936$                32765
_SYSSMU55_3550317550$                32765
..........

It is none business with the size of undo tablespace, infact there are plenty of free space：
Tablespace                    Free MB   Total MB        PCT
------------------------- ----------- ---------- ----------UNDORBS                        21,483      24000    89.5125

The DB use Automatic Undo Management (AUM) :

SQL> show parameter undo
NAME TYPE VALUE
------------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_tablespace string UNDORBS

Since it uses AUM, there is no way for us to turn the max_extents attribute:
SQL> select MAX_EXTENTS from DBA_ROLLBACK_SEGS where SEGMENT_NAME='_SYSSMU10$';
MAX_EXTENTS
-----------
32765

SQL> alter rollback segment "_SYSSMU10$" storage (MAXEXTENTs unlimited);
Rollback segment altered.

Above command was executed successful, but if we checked again, we can find the attribute still not changed:

SQL> select MAX_EXTENTS from DBA_ROLLBACK_SEGS where SEGMENT_NAME='_SYSSMU10$';
MAX_EXTENTS
-----------
32765

Even setting "_smu_debug_mode" is useless:
SQL> alter system set "_smu_debug_mode" = 4;
System altered.

SQL> alter rollback segment "_SYSSMU10$" storage (MAXEXTENTs unlimited);
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

Pay attention to this, when get ORA-1628 with AUM, in most cases, the root cause is not simply the max extents attribute too small. In most cases it is related to some bug.

Let's check our case:
SQL> select to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time, UNXPSTEALCNT, EXPSTEALCNT , NOSPACEERRCNT, TUNED_UNDORETENTION from gv$undostat;
BEGIN_TIME       UNXPSTEALCNT EXPSTEALCNT NOSPACEERRCNT TUNED_UNDORETENTION
---------------- ------------ ----------- ------------- -------------------
03/30/2012 03:24            0           0             0            23941735
03/30/2012 03:14            0           0             0            23806293
03/30/2012 03:04            0           0             0            23670851
...........
...........
03/29/2012 00:14            0           0             0            42082827
03/29/2012 00:04            0           0             0            39077268
03/28/2012 23:54         1081        1081             5             9930112
03/28/2012 23:44            0           0             9          2367948800
03/28/2012 22:14            0           0             0                   0
03/28/2012 22:04            0           0             0          2346086400

From red part, we can obviously see the auto turned undo retention is not a normal value.

And pay attention the time point with those unusual retention time, the same time the DB got the ORA-1628 error.

And it make sense now. At that very period time, oracle miss judged a very big value for auto tuned undo retention time.
Hence make many commited undo blocks keeping un-expired, and force the rollback segments growing bigger and bigger, and at last leading to:
ORA-1628: max # extents 32765 reached for rollback segment _SYSSMU6_621766836$

OK, since we find out the root cause. The solution is simple, we can set below parameter to control the undo retention time:
SQL> alter system set "_smu_debug_mode" = 33554432;
System altered.

Let's check the undo retention time again:
SQL> select to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time, UNXPSTEALCNT, EXPSTEALCNT , NOSPACEERRCNT, TUNED_UNDORETENTION from gv$undostat;
BEGIN_TIME       UNXPSTEALCNT EXPSTEALCNT NOSPACEERRCNT TUNED_UNDORETENTION
---------------- ------------ ----------- ------------- -------------------
03/30/2012 03:44            0           0             0               10800
03/30/2012 03:34            0           0             0               10800
03/30/2012 03:24            0           0             0            23941735
03/30/2012 03:14            0           0             0            23806293
03/30/2012 03:04            0           0             0            23670851
...........
...........