A strange thing at 10G undo tablespace

最新推荐文章于 2025-08-17 16:44:37 发布

转载最新推荐文章于 2025-08-17 16:44:37 发布 · 155 阅读

本文介绍了一个用于定时检查Oracle数据库中表空间空闲大小及剩余区段数的cron任务。该任务通过执行特定的SQL查询来获取SYSTEM、UNDO等表空间的信息。然而，在某些情况下，UNDOTABLESPACE未出现在查询结果中，引发了一些疑问。

We have a cronjob at 8:00 and 16:00 to check tablespace free size , use below sql
***************************************************
select tablespace_name, sum(freesize) "FREE SIZE(M)", sum(extentleft) "EXTENTS LEFT" from
(
select file_id, a.tablespace_name tablespace_name, floor((maxbytes-bytes)/next_extent) extentleft
, floor((maxbytes-bytes)/1024/1024) freesize from dba_tablespaces a, dba_data_files b where
a.tablespace_name = b.tablespace_name and maxbytes > bytes
union all
select file_id, a.tablespace_name tablespace_name, floor(bytes/next_extent) extentleft
, floor(bytes/1024/1024) freesize from dba_tablespaces a, dba_free_space b where
a.tablespace_name = b.tablespace_name
union all
select 0 file_id,tablespace_name, 0 extentleft,0 freesize from dba_tablespaces where tablespace_name
not in ( select tablespace_name from dba_free_space ) and contents = 'PERMANENT'
)
group by tablespace_name;
******************************************************************
in most time , the undo tablespace in the output result.
as below:
tablespace_name     free size    extent left
SYSTEM                        637
UNDO                            13
UTIL_IDX_S                  334           682

but sometimes at 8:00 the job output not include undo tablespace

that is really strange?

let find the root cause

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/3637/viewspace-613922/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/3637/viewspace-613922/