找出表空间上的可用空间 [英] Find out free space on tablespace

问题描述

我们的应用程序几次失败，因为"ORA-01536:超出表空间的空间配额"，我们希望能够通过定期检查表空间的可用空间并在其释放时发出警报来防止这种情况低于一定水平.

Our application has failed a few times because an 'ORA-01536: space quota exceeded for tablespace', and we would like to be able to prevent this by checking regularly the free space on the tablespace and raising an alert when it drops below certain level.

有没有办法找出表空间中剩余多少可用空间?

Is there any way to find out how much free space is left in a tablespace?

经过研究(我不是DBA)，我尝试了以下操作:

After some research (I am not a DBA), I tried the following:

select max_bytes-bytes from user_ts_quotas;

select sum(nvl(bytes,0)) from user_free_space;

但是这些查询返回的结果完全不同.

but those queries return completely different results.

推荐答案

我使用此查询

column "Tablespace" format a13
column "Used MB"    format 99,999,999
column "Free MB"    format 99,999,999
column "Total MB"   format 99,999,999
select
   fs.tablespace_name                          "Tablespace",
   (df.totalspace - fs.freespace)              "Used MB",
   fs.freespace                                "Free MB",
   df.totalspace                               "Total MB",
   round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace
   from
      dba_free_space
   group by
      tablespace_name
   ) fs
where
   df.tablespace_name = fs.tablespace_name;

这篇关于找出表空间上的可用空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！