ORA-01653,01654错误和dba_free_space视图的理解

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> ORA-01653,01654错误和dba_free_space视图的理解

上周,兄弟部门提出了一个问题,

ORA-01653/01654错误和dba_free_space视图的理解

描述如下,

开发库,对表X他们查询,或者DELETE的时候,经常出这个问题,  好像还与查询或者DELETE的数据量有关,是不是由于没建索引的原因,我查百度也没解决  select sum(bytes/1024/1024) sizeMB from dba_free_space z where z.tablespace_name=’XXX_DAT’  为null

 

首先,我们看下ORA-01654是什么错误,相应的有一个ORA-01653错误, 
ORA-01653/01654错误和dba_free_space视图的理解
ORA-01653表示某个表空间中的表段不能分配新的分区了,ORA-01654表示某个表空间的索引段不能分配新的分区了,两者含义一致,表空间容量不足了,解决方法一致,增加新的数据文件到这个表空间,另外的方法就是resize原始表空间数据文件。

错误提示的问题比较明白了,但上面兄弟问的dba_free_space记录为何为空?继续模拟此问题。

创建测试表空间和表, 
ORA-01653/01654错误和dba_free_space视图的理解
创建了1MB的表空间,表空间下创建了一张表。

检索初始表大小以及dba_free_space记录, 
ORA-01653/01654错误和dba_free_space视图的理解
其中dba_free_space显示有0.875MB剩余(按此计算,使用0.125MB),dba_extents和dba_segments显示有0.0625MB使用。

这有一些题外话的问题, 
(1) 为何dba_extents和dba_segments显示和dba_free_space不同? 
参考《Mismatch Between Free Space Reported from DBA_DATA_FILES - DBA_SEGMENTS and DBA_FREE_SPACE (文档 ID 416744.1)》 
究其原因主要为

Locally managed tablespaces files contain space metadata blocks which do not show in DBA_FREE_SPACE, DBA_EXTENTS nor DBA_SEGMENTS.

(2) 本实验使用的是11.2.0.4,按说有延迟段的特性,即表段尚未使用前,不会分配空间,为何此处分配了空间? 
原因是延迟段特性不对SYS表空间有效,我这偷懒,用的sys,若此处使用非sys则显示为, 
ORA-01653/01654错误和dba_free_space视图的理解

继续模拟实验,向TEST表INSERT了1999条记录,继续INSERT了10000条记录则报错,ORA-01653,提示表空间TBL_SMALL不能分配表段TEST, 
ORA-01653/01654错误和dba_free_space视图的理解

此时检索dba_extents和dba_segments视图, 
ORA-01653/01654错误和dba_free_space视图的理解
显示使用了0.9375MB的空间容量。

检索dba_free_space视图, 
ORA-01653/01654错误和dba_free_space视图的理解

检索dba_free_space中的表空间,发现未有TBL_SMALL, 
ORA-01653/01654错误和dba_free_space视图的理解

因此可知,表空间不能分配新的分区给表段(/索引段),则dba_free_space记录为空,因为未有free的空间可用了。

注意:若上面的INSERT语句第一次就执行where rownum10000,会报ORA-01653的错误,但此时检索dba_free_space有记录,因为第一次执行报错,语句ROLLBACK,实际表空间未被占用,因此dba_free_space有空闲空间可用。

另外,《Using DBA_FREE_SPACE (文档 ID 121259.1)》提供了一系列是用脚本,可以了解表空间使用,(仅用于教学用途,Oracle不负责任) 
ORA-01653/01654错误和dba_free_space视图的理解

总结: 
(1) ORA-01653/01654错误,基本可以判断由于表空间容量不能分配新的extent给表/索引段而导致的错误。解决方法就是新增数据文件/resize原有数据文件。 
(2) dba_free_space显示了表空间可用容量,若此时表空间容量不足,则视图中无此表空间记录。dba_free_space和dba_segments/dba_extents的计算方式不同,因此取值可能会不同。

如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:)

ORA-01653/01654错误和dba_free_space视图的理解
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> ORA-01653,01654错误和dba_free_space视图的理解


 上一篇
创建索引的两种方式比对 创建索引的两种方式比对
有一个应用,需要创建索引,创建索引一般有两种方法,一种是 CREATE INDEX ...; 一种是 CREATE INDEX ... ONLINE; 字面意思上看,一个是在线,一个是非在线,有什么不同? 1.语句执行时间的不同 创
下一篇 
MySQL基础入门之常用命令介绍 MySQL基础入门之常用命令介绍
mysql 是数据库管理命令 通过mysql –help来查看相关参数及使用说明 **mysql –help   ** **            #mysql数据库管理命令** **Usage: mysql [OPTIONS]