Skip to content

ORA-01628: max # extents (32765) for rollback seg

这个故障的情形是这样的，不停报错01628，业务挂起，回滚表空间从10g扩到32g后，也会产生同样的报错，而且回滚使用率100%。
Oracle版本： 10201
系统平台： HP-UN
这个错误我曾经在9i的版本上遭遇，但是不是bug，当时是更改回滚的storage参数设置extent限制为unlimited解决，而这里10201版本下设置为unlimited则无用，与官方文档反复效验后确认和bug 6499872吻合。
最终通过升级数据库的办法规避此bug，同时也再次说明，选择一个稳定的数据库版本是多么的重要。

官方描述如下：

Applies to:
Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.1.0.7 [Release 9.2 to 11.1]
Information in this document applies to any platform.
Details
Bug 6499872  ORA-01628: max # extents (32765) for rollback seg

 This note gives a brief overview of bug 6499872.
 The content was last updated on: 12-DEC-2008
 Click here for details of each of the sections below.
Affects:

    Product (Component) 	Oracle Server (Rdbms)
    Range of versions believed to be affected 	Versions >= 9.2 but BELOW 11.2
    Versions confirmed as being affected

        10.2.0.3
        10.2.0.2
        10.1.0.5
        9.2.0.8

    Platforms affected 	Generic (all / most platforms affected)

Fixed:

    This issue is fixed in

        11.1.0.7 (Server Patch Set)
        10.2.0.4 (Server Patch Set)

Symptoms:

Related To:

    ORA-1628



    System Managed Undo (SMU)

Description

    Once an undo segment was filled up with maxextents and the transaction got an
    ORA-1628, other transactions that bound to the same undo segment also will get same
    error code.

    REDISCOVERY INFORMATION:
    Repeated ORA-1628 errors with AUM and

    * many offline undo segments.  More than the average number of transactions
    * The undo segment referenced on error ORA-1628 will have high number of unexpired extents

    The following queries can be used to identify the bug:

    alter session set nls_date_format='mm/dd/yy hh24:mi:ss';
    set pagesize 200
    set linesize 200
    set echo on

    select sysdate from dual;

    COL undots NOPRINT NEW_VALUE p_undots FORMAT A20;
    SELECT value FROM gv$parameter WHERE name='undo_tablespace' and inst_id=sys_context('userenv','instance');

    select (nvl(sum(bytes),0)) from dba_free_space  where  tablespace_name = '&&p_undots';

    select sum(bytes)  from dba_data_files where tablespace_name = (select value from gv$parameter
    where name='undo_tablespace' and inst_id=sys_context('userenv','instance') );

    select segment_name,  nvl(sum(act),0) "ACT BYTES",    nvl(sum(unexp),0) "UNEXP BYTES", nvl(sum(exp),0) "EXP BYTES"
    from ( select segment_name,          nvl(sum(bytes),0) act,00 unexp, 00 exp
           from    DBA_UNDO_EXTENTS
           where status='ACTIVE'  and tablespace_name =   '&&p_undots'
           group by segment_name
           union
           select segment_name,00 act, nvl(sum(bytes),0) unexp, 00 exp from    DBA_UNDO_EXTENTS
           where status='UNEXPIRED'  and tablespace_name =  '&&p_undots'
           group by segment_name
           union
           select segment_name,            00 act, 00 unexp, nvl(sum(bytes),0) exp
           from    DBA_UNDO_EXTENTS
           where status='EXPIRED'  and tablespace_name =   '&&p_undots'
           group by segment_name
          ) group by segment_name;

    select status,count(*)  from dba_rollback_segs  group by status;



Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.
References

    Bug:6499872 (This link will only work for PUBLISHED bugs)
    Note:245840.1 Information on the sections in this article
Database Oracle Oracle 排障配置与调整 | Guang Cai Li | |   bug ORA-01628