Troubleshooting ORA-1628 - max # extents (32765) reached for rollback segment

Purpose of this document is to have a checklist for troubleshooting ORA-01628 errors i.e max # extents (32765) reached for rollback segment <SEGMENT_NAME> when using Automatic Undo Management (AUM). 

Oracle DB Ver 9.2.0.1 to 11.2.0.4 [Release 9.2 to 11.2] & [Release 12.1]

First: Check UNDO tablespace utilization and tuned undo retention :

Aspect of the problem can be due to long running queries which can raise tuned_undoretention to very high values and exhausts the undo tablespace resulting in ORA-1628.

So before diagnosing 1628 errors, it is important first to check UNDO tablespace utilization and tuned undo retention as follow :

SQL> SELECT DISTINCT STATUS,TABLESPACE_NAME, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS, TABLESPACE_NAME;
SQL> select max(maxquerylen),max(tuned_undoretention) from v$undostat;
SQL> select max(maxquerylen),max(tuned_undoretention) from DBA_HIST_UNDOSTAT;
SQL> select sum(bytes) from dba_free_space where tablespace_name='&UNDOTBS';

Before proceed, Invistigate/Resolve any excessive allocation of ACTIVE/UNEXPIRED extents and high calculation of tuned_undoretention.

Second: 1628 troubleshooting :

Basically, It is obvious to see high undo usage when there are huge transactions.

Here is a query you can use to find out how much undo a transaction is using:

select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from v$session a, v$transaction b
where a.saddr=b.ses_addr;

As you know, with automatic undo, you have no control over the extent sizes, nor do you have the ability to shrink them. This all happens automatically. The system automatically decides on the extent size, however it will generally start allocating larger extents if an undo segment is extending a lot. The large number of extents in the rollback segment is likely due to fragmentation in the undo tablespace: Oracle can probably only allocate extents of 64k due to fragmentation, so it is very probable to hit the max extents issue.

The maximum number of extents for undo segments is limited to 32K and a long/large runing transaction can exhaust this limit by adding new extents if the next extent of the current one is not an expired one and finally will receive ORA-1628.

So, after getting the ORA-1628 errors in the transaction that extended the undo segment until its limit, future transactions will not be allowed to bind to the undo segment until it is not shrinked (you may see that the number of extents is decreased).

So, The two major causes of ORA-1628 issue are a very large transaction or undo tablespace fragmentation.

In case of large transaction, That is solved by splitting the large transaction to smaller ones (e.g. frequent commits).

In case of undo tablespace fragmentation, That is solved by recreating the undo tablespace (this is also the recommended solution of Bug 10330444 and Bug 10229998 which were filed for the same issue and closed as not a bug).

To sum up:

The ORA-1628 error is occurring in a transaction that is generating a lot of undo data, during an add extent operation in an undo segment and is indicating we have hit the MAXEXTENTS (32765) and then we cannot extend the undo segment.

Suggested solutions

1) Set parameter "_rollback_segment_count" to online more available UNDO segments. Value should be set by placing the highest value obtained of the following queries:

select status,count(*) from dba_rollback_segs group by status; --You add OFFLINE+ONLINE to get the number

select max(maxconcurrency) from wrh$_undostat;
select max(maxconcurrency) from v$undostat;

2) In case you have large value for TUNED_UNDORETENTION :

SQL> select max(maxquerylen),max(tuned_undoretention) from v$undostat;
SQL> select max(maxquerylen),max(tuned_undoretention) from DBA_HIST_UNDOSTAT;

A fix to Bug:7291739 is to set a new hidden parameter, _HIGHTHRESHOLD_UNDORETENTION to set a high threshold for undo retention completely distinct from maxquerylen:

1. ALTER SYSTEM SET "_highthreshold_undoretention"=max(maxquerylen)+1;

3) Before/after running large transactions, Shrink undo segments when reaching certain threshold (Ex: 15000 extents) do not wait to reach its maximum (32765) to be able to bring it below certain threshold so that this undo segment can qualify for binding again.

a) select a.inst_id, a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.inst_id=b.inst_id and b.inst_id=c.inst_id
and a.ksppinm in ('_smu_debug_mode')
order by 2;
select segment_name,
round(nvl(sum(act),0)/(1024*1024*1024),3 ) "ACT GB BYTES",
round(nvl(sum(unexp),0)/(1024*1024*1024),3) "UNEXP GB BYTES",
round(nvl(sum(exp),0)/(1024*1024*1024),3) "EXP GB BYTES",
NO_OF_EXTENTS
from ( select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp, count(*) NO_OF_EXTENTS
from DBA_UNDO_EXTENTS
where status='ACTIVE' and tablespace_name = 'UNDOTBS1'
group by segment_name
union
select segment_name,00 act, nvl(sum(bytes),0) unexp, 00 exp , count(*) NO_OF_EXTENTS
from DBA_UNDO_EXTENTS
where status='UNEXPIRED' and tablespace_name = 'UNDOTBS1'
group by segment_name
union
select segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp, count(*) NO_OF_EXTENTS
from DBA_UNDO_EXTENTS
where status='EXPIRED' and tablespace_name = 'UNDOTBS1'
group by segment_name
) group by segment_name, NO_OF_EXTENTS order by 5 desc;
select sum(blocks),count(*) extents,segment_name from DBA_EXTENTS
where tablespace_name = 'UNDOTBS1' group by segment_name order by 2 desc;

b) alter system set "_smu_debug_mode" = 4 scope=memory;
c) alter rollback segment "_SYSSMU<n>$" shrink;
d) alter system set "_smu_debug_mode" = <old_value_showed_at_step_a> scope=memory;

Then you can check the result of this measure by running the query in step a again before and after the above three steps.

4) Drop and recreate undo tablespace (due to it's fragmentation) 

The steps for recreating an undo tablespace are in Note 268870.1 Ext/Pub How to Shrink the datafile of Undo Tablespace.

5) Minimize the generated undo as much as possible :

Example:

- split large transactions into smaller one

- commit more often

- use direct path load rather than conventional path load to significantly reduce the amount of undo and thus also avoid a too high fragmentation of undo tablespace.