• 博客访问: 146173
  • 博文数量: 68
  • 用 户 组: 普通用户
  • 注册时间: 2008-05-04 11:19
个人简介

暂无介绍

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类
文章存档

2010年(35)

2009年(32)

2008年(1)

我的朋友
微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: Oracle

问题症状:

ORA-1632: max # extents 4096 reached in index PERFSTAT.STATS$PARAMETER_PK
ORA-12012: error on auto execute of job 835
ORA-01632: max # extents (4096) reached in index PERFSTAT.STATS$PARAMETER_PK
ORA-06512: at "PERFSTAT.STATSPACK", line 1390
ORA-06512: at "PERFSTAT.STATSPACK", line 71
ORA-06512: at line 1

问题分析解决:

The max extents error occurs when the current number of extents equals the
maximum number of extents in the max_extents parameter for the object or the
maximum number of extents allowable for the db_block_size , whichever is
smaller, and an attempt is made to add another extent. Max extents can be set
for an object using the MAXEXTENTS option of the storage clause.
These kind of problems can be avoided by pro-actively monitoring the object
sizes that may reach their max_extents one day.

1:以下脚本发现当前extent 是否达到最大可分配值

SELECT segment_name, owner, EXTENTS, max_extents
FROM dba_segments
WHERE segment_type = 'INDEX' AND
(EXTENTS +1) >= max_extents;

2:增加最大可分配值大小

ALTER INDEX PERFSTAT.STATS$PARAMETER_PK STORAGE ( MAXEXTENTS 8192);

Overview Of ORA-01632: Max # Extents (%s) Reached in Index %s.%s [ID 152282.1]

修改时间 12-AUG-2008 类型 TROUBLESHOOTING 状态 PUBLISHED


PURPOSE
-------
This bulletin is an overview of ORA-01632 error message.

SCOPE& APPLICATION
------------------
It is for users requiring further information on ORA-01632 error message.

  Error:  ORA-01632
  Text:   max # extents (%s) reached in index %s.%s 
  ---------------------------------------------------------------
  Cause:  An index tried to extend past maxextents 
  Action: If maxextents is less than the system max, raise it.  
          Otherwise, you must recreate with larger initial, next  
          or pctincrease params.


Explanation
-----------
The max extents error occurs when the current number of extents equals the 
maximum number of extents in the max_extents parameter for the object or the 
maximum number of extents allowable for the db_block_size , whichever is 
smaller, and an attempt is made to add another extent. Max extents can be set
for an object using the MAXEXTENTS option of the storage clause.
These kind of problems can be avoided by pro-actively monitoring the object
sizes that may reach their max_extents one day.


Diagnostic Steps:
-----------------
1.  Run the following script to identify the indexes with extent problems:

SQL>select segment_name, owner, extents, max_extents 
    from dba_segments
    where segment_type = 'INDEX' and
         (extents +1) >= max_extents;

2.  Follow up the scripts in:
 Note:1019721.6  Script to Report Tables Approaching MAXEXTENTS


Possible solutions:
-------------------
1. If the value of max_extents for the object is less than the max_extents for 
   the db_block_size, then alter the STOPAGE clause of the object to allow 
   higher value for MAXEXTENTS.

ALTER INDEX .index STORAGE ( MAXEXTENTS integer); 

In databases version  7.3.X or higher one could specify maxextents 
UNLIMITED (i.e. 2147483645), but use this with caution.

ALTER INDEX .index STORAGE ( MAXEXTENTS UNLIMITED);

Remark: Note:50380.1 ALERT: Using UNLIMITED Extent Format

2. It is not advisable to have a lot (hundreds of thousands, etc.) of 
   extents in objects that could result in large amounts of of space management
   operations in dictionary managed tablespase. 
   In such case, or when the number of max_extents for the affected
   index reached the limit of maximum extents for the db_block_size,
   recreate the object with more efficient storage (larger initial, next, and/or
   pctincrease).

3. If the affected object is an auto-created LOB index, the target table needs
   to be recreated with modified initial extent for the LOB index storage clause.


References:
-----------
Note: 1025581.6   ORA-1628, ORA-1630, ORA-1631, ORA-1632, ORA-1684 MAXEXTENTS REACHED
Note: 1014514.102 ORA-01632: MAX # EXTENTS (999) REACHED IN INDEX X.SYS_IL<#>$
                    WHEN LOADING LOBS
Note: 1015356.4   HOW DO YOU CALCULATE THE LIMIT FOR MAXEXTENTS?
Note:50380.1      ALERT: Using UNLIMITED Extent Format

[@more@]

阅读(3069) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册