生产系统表空间剩余空间富足,却报ORA-01653错误解决心得

生产系统突然大规模报ORA-01653,如下图

OERR查了ORA-01653错误信息,意思是没有足够连续的空间可分配 

第一反应是查表空间可用空间,查询脚本与结果如下:

 select a.tablespace_name,trunc(a.bytes/1024/1024) "Sum MB",trunc((a.bytes-b.bytes)/1024/1024) "used MB",trunc(b.bytes/1024/1024) "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used" from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name order by ((a.bytes-b.bytes)/a.bytes) desc;

通过查询结果看,这还有800多G的空间,有点郁闷,这个问题其实已经出现多次了,前几次都是新增数据文件解决,但是治标不治本,而且不能用的剩余空间越来越大,不想这800多G就这样放着不用,下决心得好好理理,上百度查了,大部分都是让加数据文件,无果,看后台的报错信息,有的是报 ORA-01653: unable to extend table xxx.xxx by 128 in tablespace GJCXDATA,有的是报ORA-01653: unable to extend table xxx.xxx by 1024 in tablespace GJCXDATA,不明白这128和1024到底是什么鬼(半桶水),业务系统不能停太久,只能找同事增加了新的数据文件,让系统继续先,然后我重跑之前报错的建表语句,表创建成功,再查其占有的extents的情况

select * from dba_extents where segment_name='ZDGJ_T_GJYJZXZTB_UPD_WW_TMP';

TABLESPACE  EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO  
---------- ---------- ---------- ---------- ---------- ---------- ------------  
GJCXDATA            0         12    1525008      65536          8           12  
GJCXDATA            1         11    3555600      65536          8           11  
GJCXDATA            2         10    3564936      65536          8           10  
GJCXDATA            3          9    1523848      65536          8            9  
GJCXDATA            4          8     508976      65536          8            8  
GJCXDATA            5          7    1524232      65536          8            7  
GJCXDATA            6        612        144      65536          8          612  
GJCXDATA            7        611        144      65536          8          611  
GJCXDATA            8        610       9360      65536          8          610  
GJCXDATA            9        609        272      65536          8          609  
GJCXDATA           10        608      12560      65536          8          608  
GJCXDATA           11        607       2272      65536          8          607  
GJCXDATA           12        606    2540056      65536          8          606  
GJCXDATA           13        605    3555736      65536          8          605  
GJCXDATA           14        604    3555600      65536          8          604  
GJCXDATA           15        603    1523920      65536          8          603  
GJCXDATA           16        617       1152    1048576        128          617  
GJCXDATA           17        616        256    1048576        128          616  
GJCXDATA           18        615       8448    1048576        128          615  
GJCXDATA           19        614      16512    1048576        128          614  
GJCXDATA           20        613       8320    1048576        128          613  
GJCXDATA           21        612      10880    1048576        128          612  
GJCXDATA           22        611       9216    1048576        128          611  
GJCXDATA           23        610       8320    1048576        128          610  
GJCXDATA           24        609        128    1048576        128          609  
GJCXDATA           25        608       9344    1048576        128          608  
GJCXDATA           26        617       1280    1048576        128          617  
GJCXDATA           27        616        384    1048576        128          616  
GJCXDATA           28        615       8576    1048576        128          615  
GJCXDATA           29        614      16640    1048576        128          614  
GJCXDATA           30        613       8448    1048576        128          613  
GJCXDATA           31        612      11008    1048576        128          612  
GJCXDATA           32        611       9344    1048576        128          611  
GJCXDATA           33        610       8448    1048576        128          610  
GJCXDATA           34        609        384    1048576        128          609  
GJCXDATA           35        608      12672    1048576        128          608  
GJCXDATA           36        617       1408    1048576        128          617  
GJCXDATA           37        616        512    1048576        128          616  
GJCXDATA           38        615       8704    1048576        128          615  
GJCXDATA           39        614      16768    1048576        128          614  
GJCXDATA           40        613       8576    1048576        128          613  
GJCXDATA           41        612      11136    1048576        128          612  
GJCXDATA           42        611       9472    1048576        128          611  
GJCXDATA           43        610       8576    1048576        128          610  
GJCXDATA           44        609        512    1048576        128          609  
GJCXDATA           45        608      12800    1048576        128          608  
GJCXDATA           46        617       1536    1048576        128          617  
GJCXDATA           47        616        640    1048576        128          616  
GJCXDATA           48        615       8832    1048576        128          615  
GJCXDATA           49        614      16896    1048576        128          614  
GJCXDATA           50        613       8704    1048576        128          613  
GJCXDATA           51        612      11264    1048576        128          612  
GJCXDATA           52        611       9600    1048576        128          611  
GJCXDATA           53        610       8704    1048576        128          610  
GJCXDATA           54        609        640    1048576        128          609  
GJCXDATA           55        608      12928    1048576        128          608  
GJCXDATA           56        617       1664    1048576        128          617  
GJCXDATA           57        616        768    1048576        128          616  
GJCXDATA           58        615       8960    1048576        128          615  
GJCXDATA           59        614      17024    1048576        128          614  
GJCXDATA           60        613       8832    1048576        128          613  
GJCXDATA           61        612      11392    1048576        128          612  
GJCXDATA           62        611       9728    1048576        128          611  
GJCXDATA           63        610       8832    1048576        128          610  
GJCXDATA           64        609        768    1048576        128          609  
GJCXDATA           65        608      13056    1048576        128          608  
GJCXDATA           66        617       1792    1048576        128          617  
GJCXDATA           67        616        896    1048576        128          616  
GJCXDATA           68        615       9088    1048576        128          615  
GJCXDATA           69        614      17152    1048576        128          614  
GJCXDATA           70        613       8960    1048576        128          613  
GJCXDATA           71        612      11520    1048576        128          612  
GJCXDATA           72        611       9856    1048576        128          611  
GJCXDATA           73        610       8960    1048576        128          610  
GJCXDATA           74        609        896    1048576        128          609  
GJCXDATA           75        608      13184    1048576        128          608  
GJCXDATA           76        617       1920    1048576        128          617  
GJCXDATA           77        616       1024    1048576        128          616  
GJCXDATA           78        615       9216    1048576        128          615  
GJCXDATA           79        614      17280    8388608       1024          614  
GJCXDATA           80        613       9088    8388608       1024          613  
GJCXDATA           81        612      11648    8388608       1024          612  
GJCXDATA           82        611       9984    8388608       1024          611  
GJCXDATA           83        610      17920    8388608       1024          610  
GJCXDATA           84        609      36096    8388608       1024          609  
GJCXDATA           85        608      13312    8388608       1024          608  
GJCXDATA           86        617       2048    8388608       1024          617  
GJCXDATA           87        616       1152    8388608       1024          616  
GJCXDATA           88        615       9344    8388608       1024          615  
GJCXDATA           89        614      18304    8388608       1024          614  
GJCXDATA           90        613      10112    8388608       1024          613  
GJCXDATA           91        612      12672    8388608       1024          612  
GJCXDATA           92        611      11008    8388608       1024          611  
GJCXDATA           93        610      18944    8388608       1024          610  
GJCXDATA           94        609      37120    8388608       1024          609  
GJCXDATA           95        608      14336    8388608       1024          608  
GJCXDATA           96        617       4096    8388608       1024          617  
GJCXDATA           97        616       2176    8388608       1024          616  
GJCXDATA           98        615      10368    8388608       1024          615  
GJCXDATA           99        614      19328    8388608       1024          614  
GJCXDATA          100        613      11136    8388608       1024          613  
GJCXDATA          101        612      13696    8388608       1024          612  
GJCXDATA          102        611      12032    8388608       1024          611  

GJCXDATA          103        610      19968    8388608       1024          610  

extents分配是从8个block为起点,分配一段后如果还有数据oracle自动算出下个extents分配的blocks数量,这个跟表的INITIAL_EXTENT和NEXT_EXTENT什么关系目前还没研究透,这时候想之前报错的128是不是oracle要需要分配128blocks的连续空间,但是找不到报出来的呢?

通过统计dba_free_space进行验证

select blocks,count(*),count(*)*blocks*8/1024 from dba_free_space a where tablespace_name='GJCXDATA' group by blocks order by blocks;  

果然,创建ZDGJ_T_GJYJZXZTB_UPD_TMP_WW需要63个大于等于128blocks的空间空间,但是剩余空间里没有这么多,进而就报错了,从这个查询结果可以看出,112个block和120个block的空间最多,合起来将近700G了,到这里就可以解释为什么查表空间可用空间有800G,但是创建表还报错了,因为可用空间太碎了.

可用空间这么碎的情况也是第一次遇到,觉得应该是个别过程导致的,接下来,就要找出谁是导致这么碎可用空间的罪魁祸首.....

通过分析dba_free_space和dba_extents,block_id 在file_id每个文件是顺序的,通过block_id与blocks可以判断出某个数据文件哪一段是可用的,可用空间bytes为blocks*每个block的大小,大部分数据库都是8k,可通过查看dba_tablespaces.BLOCK_SIZE获取,同理在dba_extents通过file_id,block_id,blocks可以查出某个数据文件哪一段被使用了,以及使用的对象等信息

接下来通过分析dba_extents和db_free_space 来看看是什么把可用空间用的这么碎

先通过以下脚本找出blocks是120 数量最多的file_id

select * from (select FILE_ID,count(*) from dba_free_space a WHERE BLOCKS=120 group by FILE_ID ORDER BY COUNT(*) desc) where rownum<5;

就以301文件示例进行分析,查出301文件现在可用空间情况

select * from (select * from dba_free_space where file_id=301 order by block_id) where rownum<11;

从block_id 264开始120个blocks剩余空间,block_id 384--400被占用,查查这个被谁占用了,查出结果为ZDJKDS_T_CSWJXXB1

select * from dba_extents where block_id>(264+119) and block_id<400 and file_id=301;

再查block_id 512-912的占用情况

select * from dba_extents_0409 where block_id>(400+111) and block_id<912 and file_id=301;

还是有ZDJKDS_T_CSWJXXB1的身影,继续

select * from dba_extents_0409 where block_id>(912+111) and block_id<1160 and file_id=301;

...

一直都有ZDJKDS_T_CSWJXXB1,而且每次分配的blocks 都是8blocks,16blocks,到这里想会不会是这个到导致的空间太碎

SELECT COUNT(*) FROM ZDJKDS_T_CSWJXXB1;
  COUNT(*)
----------
   5182228

 select partition_name,bytes/1024/1024 from user_segments where segment_name=upper('ZDJKDS_T_CSWJXXB') order by partition_name

PARTITION_NAME                 BYTES/1024/1024
------------------------------ ---------------

                                             105933.688

这个表字段不多,500多万的记录占用了100多g,有点夸张了,看看这个表的extents分布情况

SELECT blocks,count(*) FROM DBA_EXTENTS where segment_name='ZDJKDS_T_CSWJXXB1' group by blocks order by count(*);

从查询结果可以看出,这个表8个blocks的extents有46万多,16blocks有34万多,实在太碎了,至此,问题水落石出了,就是这个表把表空间可用空间分割的太碎.

后面咨询开发工程师,这个表插入频次30秒插入一次,每次插入的量又很少,导致了这个问题

问题找到了,解决起来就简单了,把这个表重建到其他表空间,再把原表drop,让可用的空间能连接起来,处理完再去看dba_free_space,112和120的已经很少了,连续的可用空间多了很多,并且与开发人员沟通可以将插入频次延长,改为5分钟插入一次,到此问题得以解决

总结:

之前对tablespace,table,segment,extent,block的理解关联关系懵懵懂懂,经过这次问题排查,加深了理解,日常运行监控,其实可以增加对空间分配很碎的对象进行监控,可提前发现问题,脚本如下

SELECT segment_name,partition_name,count(*) FROM DBA_EXTENTS where blocks<64 group by segment_name,partition_name having count(*)>100;