索引表空间不足，数据无法插入和更新

阳光穿透心脏的1/2处 2022-06-16 08:50 229阅读 0赞

今天发现数据库里报错，索引表空间空间不足，达到最大的32G的限制了，第一感觉就是索引不能更新，数据无法入库，可是看了点数据，似乎不太对，之前没有深入研究过此类现象，于是自己做了个测试来验证数据和索引是否是一个事务整体。以下模拟建立数据空间和索引空间，分配不同的大小进行验证。

SQL> create tablespace ts\_data datafile '/app/ora\_server/ora\_base/oradata/gispub/ts\_data.dbf' size 5M  autoextend on next 50M MAXSIZE UNLIMITED;

Tablespace created  
   
   
SQL>  create tablespace ts\_index datafile '/app/ora\_server/ora\_base/oradata/gispub/ts\_index.dbf ' size 5M;

Tablespace created

SQL> CREATE table ts\_table(  
  2  OWNER VARCHAR2(30),  
  3  OBJECT\_NAME    VARCHAR2(128) ,  
  4  SUBOBJECT\_NAME VARCHAR2(30),  
  5  OBJECT\_ID NUMBER,  
  6  DATA\_OBJECT\_ID NUMBER,  
  7  OBJECT\_TYPE    VARCHAR2(19),  
  8  CREATED DATE ,  
  9  LAST\_DDL\_TIME  DATE ,  
 10  TIMESTAMP      VARCHAR2(19),  
 11  STATUS         VARCHAR2(7) ,  
 12  TEMPORARY      VARCHAR2(1),  
 13  GENERATED      VARCHAR2(1),  
 14  SECONDARY      VARCHAR2(1))  
 15  tablespace ts\_data;  
   
Table created

SQL> select segment\_name,bytes/1024/1024 from dba\_segments where owner='SCOTT' AND SEGMENT\_NAME='TS\_TABLE';  
   
SEGMENT\_NAME                                                                     BYTES/1024/1024  
\-------------------------------------------------------------------------------- ---------------  
TS\_TABLE                                                                                  0.0625

\-- Create/Recreate indexes

SQL> create index idx\_tt\_owner\_name on TS\_TABLE (owner, object\_name)  
  2    tablespace TS\_INDEX;  
   
Index created

SQL> insert into ts\_table  select \* from dba\_objects;  
   
49264 rows inserted  
   
SQL> COMMIT;  
   
Commit complete  
   
SQL> select segment\_name,bytes/1024/1024 from dba\_segments where owner='SCOTT' AND SEGMENT\_NAME='TS\_TABLE';  
   
SEGMENT\_NAME                                                                     BYTES/1024/1024  
\-------------------------------------------------------------------------------- ---------------  
TS\_TABLE                                                                                       6  
   
SQL> select segment\_name,bytes/1024/1024 from dba\_segments where owner='SCOTT' AND SEGMENT\_NAME='IDX\_TT\_OWNER\_NAME';  
   
SEGMENT\_NAME                                                                     BYTES/1024/1024  
\-------------------------------------------------------------------------------- ---------------  
IDX\_TT\_OWNER\_NAME                                                                              4

第一次提交数据，数据和索引有足够的空间写入数据，查看段的大小情况如上。  
  
SQL> insert into ts\_table  select \* from dba\_objects;  
   
insert into ts\_table  select \* from dba\_objects  
   
ORA-01654: unable to extend index SCOTT.IDX\_TT\_OWNER\_NAME by 128 in tablespace TS\_INDEX

SQL> select segment\_name,bytes/1024/1024 from dba\_segments where owner='SCOTT' AND SEGMENT\_NAME='IDX\_TT\_OWNER\_NAME';  
   
SEGMENT\_NAME                                                                     BYTES/1024/1024  
\-------------------------------------------------------------------------------- ---------------  
IDX\_TT\_OWNER\_NAME                                                                              4  
   
SQL> select segment\_name,bytes/1024/1024 from dba\_segments where owner='SCOTT' AND SEGMENT\_NAME='TS\_TABLE';  
   
SEGMENT\_NAME                                                                     BYTES/1024/1024  
\-------------------------------------------------------------------------------- ---------------  
TS\_TABLE                                                                                       8

此时再次插入数据，由于索引空间满了，无法继续扩展，此时抛出异常，这个时候索引的段依旧是4M，但是再次查询表的段大小的时候确发现段大小变大了，然而数据是否真的写入表中了呢？

SQL> select count(\*) from ts\_table;  
   
  COUNT(\*)  
\----------  
     49264

这个数字说明了是第一次插入到数据库的数量，这样可以看出数据库的基本工作原理，首先先扩展表数据存储段，然后再扩展索引段，然后仔细看数据的变化为什么是从6变到8，我手动更改了下索引存储表空间的大小，看看可以的话索引段的扩展情况。

SQL> ALTER DATABASE DATAFILE '/app/ora\_server/ora\_base/oradata/gispub/ts\_index.dbf '  RESIZE 8M;  
   
Database altered  
   
SQL> insert into ts\_table  select \* from dba\_objects;  
   
49264 rows inserted  
   
SQL> select segment\_name,bytes/1024/1024 from dba\_segments where owner='SCOTT' AND SEGMENT\_NAME='IDX\_TT\_OWNER\_NAME';  
   
SEGMENT\_NAME                                                                     BYTES/1024/1024  
\-------------------------------------------------------------------------------- ---------------  
IDX\_TT\_OWNER\_NAME                                                                              7  
   
SQL> select segment\_name,bytes/1024/1024 from dba\_segments where owner='SCOTT' AND SEGMENT\_NAME='TS\_TABLE';  
   
SEGMENT\_NAME                                                                     BYTES/1024/1024  
\-------------------------------------------------------------------------------- ---------------  
TS\_TABLE                                                                                      11

至于数据段是如何分配的，这个稍后处理说明。

这个实验说明了数据库当索引和数据表空间中有一个无法插入的时候，数据或许索引是一个事务整体，无法单独插入数据或许更新索引。