ORA-01654 错误的解决方法

ORA-01654: unable to extend index BO.INDEX_indexname by 311072 in tablespace 错误，上网查原因，发现解决之道只有一个，就

引言：

数据库突然报：　ORA-01654: unable to extend index BO.INDEX_indexname by 311072 in tablespace　错误，上网查原因，发现解决之道只有一个，就是增加tablespace的大小．因我的database的tablespace已经足够大了，最后发现不是tablespace不足，而是参数错误．现归纳解决此问题的方法如下．

方法１：

　　　当出现类似错误时，首先检查tablespace的空间是否足够大，如果不够大，说明tablespace的空间不够扩展了，这时候需要将tablespace的datafile的size变大，方法很简单我就不讲了，或增加新的datafile到此tablespace中，使用alter tablespace mytablespace add datafile 'XXX' size xxxx就ＯK啦．

方法2:

　　　这就是我这此遇到的问题．我的datafile的size为2000m，而我的index的next extent为2G，pct increase为50,这样一来下一个要扩展的extent为3G,而我的datafile的Size为２G,故无发找到连续3G的空间，当然会出错．

　　　问题找到了，解决当然很简单，修改next extent 为128k,pct increase为0,问题解决.

不知道是谁设定的,真是个低级错误.

---------------------------------------------分割线---------------------------------------------

问题现象：

测试库使用如下方式创建索引：

create index IDX_ANA_OFFICE on ANA (OFFICE_CITY, OFFICE_NO)
tablespace IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);

报错：ORA-01654: unable to extend index GALT.IDX_OFFICE by 128 in tablespace IDX

 

改为默认创建：

create index IDX_ANA_PNR_OFFICE on ANA (OFFICE_CITY, OFFICE_NO)
tablespace IDX;

 

查看SQL是：

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

问题追查：

1、首先针对1654这个报错，MOS是这样介绍的：

Error: ORA-01654 Text: unable to extend index %s.%s by %s in tablespace %s ------------------------------------------------------------------------------- Cause: Failed to allocate extent for index segment in tablespace. Action: Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the specified tablespace (1)、针对表空间不足的情况，建议使用DBA_FREE_SPACE视图进行查询(Note: 121259.1提供了若干脚本)。

(2)、另外，针对索引的问题，DBA_INDEXES视图则描述了下一个分区(NEXT_EXTENT)的大小，以及所有索引的百分比增长(PCT_INCREASE)。“next_extent”指的是试图分配的区大小(也就是报错中涉及的内容)。

区分配计算：next_extent = next_extent * (1 + (pct_increase/100))

在Concept中描述了为段分配区的算法

How Extents Are Allocated

Oracle uses different algorithms to allocate extents, depending on whether they are locally managed or dictionary managed. With locally managed tablespaces, Oracle looks for free space to allocate to a new extent by first determining a candidate datafile in the tablespace and then searching the datafile’s bitmap for the required number of adjacent free blocks. If that datafile does not have enough adjacent free space, then Oracle looks in another datafile.

MOS也提出了若干可能的解决方法：

Possible solutions: ------------------ - Manually coalesce adjacent free extents: ALTER TABLESPACE COALESCE; The extents must be adjacent to each other for this to work. - Add a datafile: ALTER TABLESPACE ADD DATAFILE '' SIZE ; - Resize the datafile: ALTER DATABASE DATAFILE '' RESIZE ; - Enable autoextend: ALTER DATABASE DATAFILE '' AUTOEXTEND ON MAXSIZE UNLIMITED; - Defragment the Tablespace - Lower "next_extent" and/or "pct_increase" size: ALTER STORAGE ( next pctincrease );

下面这句话我认为是重点：

“这个错误并未指出表空间中是否有足够的空间，仅仅说明Oracle不能找到一个足够大的连续空间用来匹配next extent。

 

 

2、另一篇文章“TROUBLESHOOTING GUIDE (TSG) - UNABLE TO CREATE / EXTEND Errors”说明了各种关于“UNABLE TO CREATE / EXTEND”的错误。

“unable to extend"的错误是指当没有足够连续的空间用来分配段的情况。

 

I. 提出了解决这种错误所需要的信息：

(1)、判断报错表空间中最大的连续空间是多少。

SELECT max(bytes) FROM dba_free_space WHERE tablespace_name = '';

这个SQL返回的是表空间最大允许的连续块大小。(DBA_FREE_SPACE不会返回临时表空间的信息，可以参考“DBA_FREE_SPACE Does not Show Information about Temporary Tablespaces (文档 ID 188610.1)”这篇文章会介绍如何查看临时表空间的连续块大小)。

如果在这个报错之后立即执行上述SQL，则返回的表空间中连续的最大块会小于这个对象正在试图分配的next extent的空间。

 

(2)、判断NEXT_EXTENT大小。

a) 对于PCT_INCREASE=0的字典管理表空间(DMT)或者使用统一UNIFORM区管理的本地管理表空间(LMT)，使用如下SQL：