Oracle\故障排查\ORA 数据库报错：“ORA-01654: 索引。。。无法通过8192（在表空间。。。中）

穷苦书生_万事愁

于 2024-07-24 08:30:00 发布

阅读量1.2k

点赞数 24

分类专栏：数据库文章标签： oracle 数据库

本文链接：https://blog.csdn.net/weixin_tank88921/article/details/140584070

版权

数据库专栏收录该内容

89 篇文章 1 订阅

订阅专栏

报错

[oracle@testos:/home/oracle]$oerr ora 01654
01654, 00000, "unable to extend index %s.%s by %s in tablespace %s"
// *Cause:  Failed to allocate an extent of the required number of blocks for
//          an index segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//          files to the tablespace indicated.
[oracle@testos:/home/oracle]$

数据库报错：“ORA-01654: 索引。。。无法通过8192（在表空间。。。中）扩展”

或者：ora-01652无法通过128(在表空间temp中)扩展temp段，这种错误信息时，表明数据库表空间文件或者临时表空间文件已经达到上限；

解决办法：增加表空间文件，sql语句如

alter tablespace temp add tempfile '/oracle/database/oradata/orcl/temp02.dbf' size 10240m autoextend on next 1024m maxsize 30G;

常识1

表空间数据文件容量与DB_BLOCK_SIZE有关，在初始建库时，DB_BLOCK_SIZE要根据实际需要，设置为 4K、8K、16K、32K、64K等几种大小，ORACLE的物理文件最大只允许4194304个数据块（由操作系统决定），表空间数据文件的最大值为 4194304×DB_BLOCK_SIZE/1024M。

sql查看DB_BLOCK_SIZE值：

sys@testdb(64)> select value/1024 as "kb" from v$parameter where name='db_block_size';

        kb
----------
         8

可以看到这个数据库DB_BLOCK_SIZE值为8k，所以单个表空间文件最大值为8K*2^22 = 32G，由此可推出：

2K = 8G、8K = 32G、16K = 64G、32K = 128G；

DB_BLOCK_SIZE作为数据库的最小操作单位，是在创建数据库的时候指定的，在创建完数据库之后便不可修改。要修改DB_BLOCK_SIZE，需要重建数据库。一般可以将数据EXP出来，然后重建数据库，指定新的DB_BLOCK_SIZE，然后再将数据IMP进数据库。
DB_BLOCK_SIZE一般设置为操作系统块的倍数，即2K,4K,8K,16K或32K，但它的大小一般受数据库用途的影响。对于联机事务，其特点是事务量大，但每个事务处理的数据量小，所以DB_BLOCK_SIZE设置小点就足够了，一般为4K或者8K，设置太大话一次读出的数据有部分是没用的，会拖慢数据库的读写时间，同时增加无必要的IO操作。而对于数据仓库和ERP方面的应用，每个事务处理的数据量很大，所以DB_BLOCK_SIZE一般设置得比较大，一般为8K，16K或者32K，此时如果DB_BLOCK_SIZE小的话，那么I/O自然就多，消耗太大。
大一点的DB_BLOCK_SIZE对索引的性能有一定的提高。因为DB_BLOCK_SIZE比较大的话，一个DB_BLOCK一次能够索引的行数就比较多。
对于行比较大的话，比如一个DB_BLOCK放不下一行，数据库在读取数据的时候就需要进行行链接，从而影响读取性能。此时DB_BLOCK_SIZE大一点的话就可以避免这种情况的发生

常识2

如果单个数据库表空间文件大小超过要导入的数据库表空间最大大小，比如1T的数据库文件需要导入，解决办法：

创建bigfile大文件表空间：在oracle11g中引进了bigfile表空间，他充分利用了64位CPU的寻址能力，使oracle可以管理的数据文件总量达到8EB。单个数据文件的大小达到128TB，即使默认8K的db_block_size也达到了32TB。

所谓Bigfile Tablespace最显著的差别就是一个表空间只能对应一个数据文件。Bigfile Tablespace虽只对应一个数据文件，但数据文件对应的最大体积大大增加。传统的small datafile每个文件中最多包括4M个数据块，按照一个数据块8K的大小核算，最大文件大小为32G。每个Small Tablespace理论上能够包括1024个数据文件，这样计算理论的最大值为32TB大小。而Bigfile Datafile具有更强大的数据块block容纳能力，最多能够包括4G个数据块。同样按照数据块8K计算，Bigfile Datafile大小为32KG=32TB。理论上small tablespace和big tablespace总容量相同

bigfile tablespace 设置不同大小的db_block_size时数据文件的最大值：

db_block_size=2  KB   2 KB*4G=8T
db_block_size=4  KB   4 KB*4G=16T
db_block_size=8  KB   8 KB*4G=32T    8*1024*4G=8*4TB=32TB
db_block_size=16 KB   16KB*4G=64T
db_block_size=32 KB   32KB*4G=128T

需要注意的是：使用bigfile表空间，它只能支持一个数据文件。也就是说这个文件的最大大小就是表空间最大大小，你不可能通过增加数据文件来扩大该表空间的大小。

所以需要根据实际情况，在建数据库时指定db_block_size的大小，要不然建完数据库后无法修改

报错延伸

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

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、问题追查：

针对表空间不足的情况，建议使用DBA_FREE_SPACE视图进行查询(Note: 121259.1提供了若干脚本)。
另外，针对索引的问题，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 <tablespace name> COALESCE;
  The extents must be adjacent to each other for this to work.

-- Add a datafile: 
        ALTER TABLESPACE <tablespace name> ADD DATAFILE '<full path and file 
        name>' SIZE <integer> <k|m>; 

-- Resize the datafile: 
        ALTER DATABASE DATAFILE '<full path and file name>' RESIZE <integer> <k|m>; 

-- Enable autoextend: 
        ALTER DATABASE DATAFILE '<full path and file name>' AUTOEXTEND ON 
        MAXSIZE UNLIMITED;

-- Defragment the Tablespace

-- Lower "next_extent" and/or "pct_increase" size:
        ALTER <segment_type> <segment_name> STORAGE ( next <integer> <k|m> 
        pctincrease <integer>);

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

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

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

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

（1）判断报错表空间中最大的连续空间是多少。

SELECT max(bytes) FROM dba_free_space WHERE tablespace_name = '<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

SELECT NEXT_EXTENT, PCT_INCREASE 
 FROM DBA_SEGMENTS 
 WHERE SEGMENT_NAME = <segment name> 
 AND SEGMENT_TYPE = <segment type> 
 AND OWNER = <owner> 
 AND TABLESPACE_NAME = <tablespace name>;

其中segment_type会展示在错误信息中，可能包含如下类型的segment：

CLUSTER
INDEX
INDEX PARTITION
LOB PARTITION
LOBINDEX
LOBSEGMENT
NESTED TABLE
ROLLBACK
TABLE
TABLE PARTITION
TYPE2 UNDO
TYPE2 UNDO (ORA-1651)

同样地，segment_name可以在错误信息中找到。

b）对于使用SYSTEM|AUTOALLOCATE区管理的本地管理表空间(LMT)。

没有方法可以查询它的next extent大小。只能查询错误信息，错误信息中的块数乘以表空间的块大小，以此来判断需要创建的区大小。

c）对于PCT_INCREASE>0的字典管理表空间(DMT)。

SELECT EXTENT_MANAGEMENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = '<tablespace name>';
-- 使用如下公式计算需要分配的区大小：
extent size = next_extent * (1 + (pct_increase/100)
-- 例如
next_extent = 512000
pct_increase = 50
next extent size = 512000 * (1 + (50/100)) = 512000 * 1.5 = 768000

注意：

ORA-01650 Rollback Segment

pct_increase仅用于Oracle若干早期版本，后面版本中回滚段的pct_increase默认是0。

ORA-01652 Temporary Segment

临时段与表空间创建的存储默认值相同。

如果查询出现错误，则需要判断这个查询语句是否尽可能地最优以完成排序。

（3）判断表空间是否包含了AUTOEXTENSIBLE，并已经达到MAXSIZ。

-- 对于数据文件
SELECT file_name, bytes, autoextensible, maxbytes FROM dba_data_files WHERE tablespace_name='<tablespace name> '; 
-- 对于临时文件
SELECT file_name, bytes, autoextensible, maxbytes FROM dba_temp_files WHERE tablespace_name='<tablespace name> ';

（4）判断哪种解决方法最优

如果NEXT EXTENT的容量(步骤2或3)大于空闲空间最大的连续块，那么“Manually Coalesce Adjacent Free Extents”是个选择。如果coalesce后仍旧没有足够的连续空间，那么可能需要其他的选项。

如果表空间的数据文件/临时文件的卷有足够的空间，那么添加数据文件/临时文件或消除表空间碎片化可能管用，将这个文件添加到新卷中。

如果表空间是AUTOEXTENSIBLE并且已经MAXSIZE，那么需要提高最大容量(确认有足够的卷空间)，或者添加数据文件/临时文件，或者消除碎片化。

如果NEXT EXTENT的容量(步骤2或3)小于空闲空间最大的连续块，那么就需要联系Oracle支持。

可能的解决方案：

手工合并相邻的空闲区

ALTER TABLESPACE <tablespace name> COALESCE;

将一个或多个数据文件/临时文件修改为使用AUTOEXTEND
```
ALTER DATABASE DATAFILE|TEMPFILE '<full path and name>' AUTOEXTEND ON MAXSIZE <integer> <k|m|g|
```
注意：强烈建议明确MAXSIZE参数，防止数据文件/临时文件消耗卷上的所有可用空间。

添加数据文件/临时文件

ALTER TABLESPACE <tablespace name> ADD DATAFILE|TEMPFILE '<full path and file name>' SIZE <integer> <k|m|g|t|p|e>;

如果段是字典管理表空间，可以降低“next_extent”和/或“pct_increase”的大小

-- 对于非临时段和非分区段：
ALTER <SEGMENT TYPE> <segment_name> STORAGE ( next <integer> <k | m | g | t | p | e> pctincrease <integer>); 
-- 对于非临时段和分区段：
ALTER TABLE <table_name> MODIFY PARTITION <partition_name> STORAGE ( next <integer> <k | m | g | t | p | e> pctincrease <integer>);
-- 对于临时段：
ALTER TABLESPACE <tablespace name> DEFAULT STORAGE (initial <integer> <k | m | g | t | p | e> next <integer> <k | m | g | t | p | e> pctincrease <integer>);

重改数据文件/临时文件的大小

ALTER DATABASE DATAFILE|TEMPFILE '<full path and file name>' RESIZE <integer> <k | m | g | t | p | e>;

消除表空间的碎片

附录：和此类解决方法相关的报错：

ORA-1650: unable to extend rollback segment %s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for a rollback segment in the tablespace.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1651: unable to extend save undo segment by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for saving undo entries for the indicated offline tablespace.
  Action: Check the storage parameters for the SYSTEM tablespace. The tablespace needs to be brought back online so the undo can be applied.

ORA-1652: unable to extend temp segment by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1653: unable to extend table %s.%s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for a table segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1654: unable to extend index %s.%s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for an index segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1655: unable to extend cluster %s.%s by %s for tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for a cluster segment in tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1658: unable to create INITIAL extent for segment in tablespace %s
  Cause: Failed to find sufficient contiguous space to allocate INITIAL extent for segment being created.
  Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with a smaller value for INITIAL

ORA-1659 unable to allocate MINEXTENTS beyond %s in tablespace %s
  Cause: Failed to find sufficient contiguous space to allocate MINEXTENTS for the segment being created.
  Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with smaller value for MINEXTENTS, NEXT or PCTINCREASE

ORA-1683: unable to extend index %s.%s partition %s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for index segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1688: unable to extend table %s.%s partition %s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for table segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1691: unable to extend lob segment %s.%s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for LOB segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1692: unable to extend lob segment %s.%s partition %s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for LOB segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.


ORA-3233: unable to extend table %s.%s subpartition %s by %s in tablespace %s
  Cause: Failed to allocate an extent for table subpartition segment in tablespace.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-3234: unable to extend index %s.%s subpartition %s by %s in tablespace %s
  Cause: Failed to allocate an extent for index subpartition segment in tablespace.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-3238: unable to extend LOB segment %s.%s subpartition %s by %s in tablespace %s
   Cause: An attempt was made to allocate an extent for LOB subpartition segment in tablespace, but the extent could not be allocated because there is not enough space in the tablespace indicated.
   Action: Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

总结：

针对上面案例中的错误，总体讲是空间不足导致的，之所以使用第二个SQL可以，原因可能就是这种参数值设置下的满足可以空闲空间连续块的容量，上面采用的是减小extent分配大小的方式，另外上面提到的扩大文件、修改参数值、消除碎片化等方法都可以尝试使用。

穷苦书生_万事愁

关注

24
点赞
踩
29

收藏

觉得还不错? 一键收藏
0
评论
Oracle\故障排查\ORA 数据库报错：“ORA-01654: 索引。。。无法通过8192（在表空间。。。中）

数据库报错：“ORA-01654: 索引。。。无法通过8192（在表空间。。。中）扩展”或者：ora-01652无法通过128(在表空间temp中)扩展temp段，这种错误信息时，表明数据库表空间文件或者临时表空间文件已经达到上限；解决办法：增加表空间文件，sql语句如。
复制链接

扫一扫

专栏目录