今天巡检时发现users表空间的使用率超过了100%:
本以为是巡检的SQL写的有问题,巡检SQL如下:
select a.tablespace_name "表空间名称",
g.autoextensible "是否自动扩展",
round((a.s - f.s),2) "已用空间(G)",
round(f.s,2) "空闲的已分配空间(G)",
round(a.ms,2) "表空间大小(G)",
round((a.ms - (a.s - f.s)),2) "剩余空间(G)",
round(a.s,2) "已分配大小(G)",
round((a.s - f.s) / a.ms * 100,2) "使用率%"
from (select d.tablespace_name,
sum(bytes / 1024 /1024 /1024) s,
sum(decode(maxbytes,0,bytes,maxbytes) / 1024 / 1024 / 1024) ms
from dba_data_files d
group by d.tablespace_name) a,
(select f.tablespace_name,sum(f.bytes / 1024 /1024 /1024) s
from dba_free_space f
group by f.tablespace_name) f,
(select distinct tablespace_name,autoextensible
from dba_data_files
where autoextensible = 'YES'
union
select distinct tablespace_name,autoextensible
from dba_data_files
where autoextensible = 'NO'
and tablespace_name not in
(select distinct tablespace_name
from dba_data_files
where autoextensible = 'YES')) g
where a.tablespace_name=f.tablespace_name
and g.tablespace_name=f.tablespace_name order by "使用率%";
仔细检查n遍,都查不出问题。那么就查一下users表空间的具体情况:
SQL> select file_name,bytes/1024/1024/1024 a,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 b from dba_data_files where tablespace_name='USERS';
FILE_NAME A AUT B
------------------------------------------------------------ ---------- --- ----------
+RACDB_DATA/racdb/datafile/users.300.1074430251 31.9375 YES 31.9999847
+RACDB_DATA/racdb/datafile/users01.dbf 16 YES 8
+RACDB_DATA/racdb/datafile/users.259.1074430775 13.6875 YES 31.9999847
+RACDB_DATA/racdb/datafile/users.443.1074429181 31.9375 YES 31.9999847
+RACDB_DATA/racdb/datafile/users.436.1074433299 8 YES 31.9999847
+RACDB_DATA/racdb/datafile/users.358.1074433229 8 NO 0
+RACDB_DATA/racdb/datafile/users.404.1074433269 8 NO 0
7 rows selected.
找到了原因所在,发现users01这个文件的bytes竟然大于maxbytes!匪夷所思,不得其解。
在看到这几个blog以后,明白了其中的原因。
dba_data_files里的bytes大于maxbytes
oracle可视化工具IB,oracle小记:dba_data_files
【DBA】在dba_data_files视图中,bytes大于maxbytes 说明
原因是 手动resize数据文件会导致这一现象。
仔细点说,所有的datafile有这几种情况
- 当autoextend=NO时:maxbytes为0,bytes即为该数据文件的初始化大小,也为该文件最大大小。
- 当autoextend=YES时:
(1)如果bytes>maxbytes,则说明该数据文件进行了resize,resize即为该数据文件可使用的最大大小,因为被更新的列只是bytes列,maxbytes的值不会被更新。所以即使没有达到32G,也无法自动扩展
(2)如果bytes=maxbytes,则该数据文件可使用大小为bytes的值,无法扩展(受maxbytes限制)
而dba_data_files中的maxbytes的更新需要通过命令alter database来重新设置该大小。
这个文件当时最开始create的时候是8G,且不能自动扩展。后期resize成了16G。再后来由不可自动扩展改为可自动扩展。
因此出现了maxbytes小于bytes的情况。
找到了问题所在,就可以对这个datafile进行重新的修改,这样巡检时就不会再出现使用率超过100%的情况了。
SQL> alter database datafile '+RACDB_DATA/racdb/datafile/users01.dbf' AUTOEXTEND ON MAXSIZE 31G;
Database altered.
查看结果:
SQL> select file_name,bytes/1024/1024/1024 SIZE_G,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 max_G from dba_data_files where tablespace_name=D FILE_ID=152;
FILE_NAME SIZE_G AUT MAX_G
------------------------------------------------------------ ---------- --- ----------
+RACDB_DATA/racdb/datafile/users01.dbf 16 YES 31
再检查下有没有其他的数据文件有这个问题:
SQL> select file_name,bytes/1024/1024/1024 SIZE_G,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 max_G from dba_data_files where bytes>MAXBYTES aensible='YES';
no rows selected