找出表空间上的可用空间

Question

我们的应用程序因“ORA-01536：超出表空间的空间配额”而失败了几次，我们希望能够通过定期检查表空间上的可用空间并在其低于特定水平时发出警报来防止这种情况发生。

有什么方法可以找出表空间中剩余多少可用空间？

经过一些研究（我不是 DBA），我尝试了以下操作：

select max_bytes-bytes from user_ts_quotas;

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

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

Answer 1

我用这个查询

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;

Answer 2

更准确的 SQL 语句

SELECT  a.tablespace_name,
    ROUND (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES) * 100,2) percentage_used,
    c.BYTES / 1024 / 1024 space_allocated,
    ROUND (c.BYTES / 1024 / 1024 - NVL (b.BYTES, 0) / 1024 / 1024,2) space_used,
    ROUND (NVL (b.BYTES, 0) / 1024 / 1024, 2) space_free, 
    c.DATAFILES
  FROM dba_tablespaces a,
       (    SELECT   tablespace_name, 
                  SUM (BYTES) BYTES
           FROM   dba_free_space
       GROUP BY   tablespace_name
       ) b,
      (    SELECT   COUNT (1) DATAFILES, 
                  SUM (BYTES) BYTES, 
                  tablespace_name
           FROM   dba_data_files
       GROUP BY   tablespace_name
    ) c
  WHERE b.tablespace_name(+) = a.tablespace_name 
    AND c.tablespace_name(+) = a.tablespace_name
ORDER BY NVL (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES), 0) DESC;

Answer 3

有很多方法可以检查大小，但作为开发人员，我们没有太多权限查询元表，我发现这个解决方案非常简单
（注意：如果您收到错误消息 ORA-01653
“ORA-01653 错误是因为您需要向表空间添加空间而引起的。”）

--Size of All Table Space

--1. Used Space
SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS "USED SPACE(IN GB)" FROM USER_SEGMENTS GROUP BY TABLESPACE_NAME
--2. Free Space
SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS "FREE SPACE(IN GB)" FROM   USER_FREE_SPACE GROUP BY TABLESPACE_NAME

--3. Both Free & Used
SELECT USED.TABLESPACE_NAME, USED.USED_BYTES AS "USED SPACE(IN GB)",  FREE.FREE_BYTES AS "FREE SPACE(IN GB)"
FROM
(SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS USED_BYTES FROM USER_SEGMENTS GROUP BY TABLESPACE_NAME) USED
INNER JOIN
(SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS FREE_BYTES FROM  USER_FREE_SPACE GROUP BY TABLESPACE_NAME) FREE
ON (USED.TABLESPACE_NAME = FREE.TABLESPACE_NAME);

谢谢

Answer 4

这是我遇到的最简单的查询之一，我们也用它来监控：

SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024/1024 "FREE SPACE(GB)"
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

一篇关于 Oracle 表空间的完整文章：
表空间

Answer 5

这也很不错

clear breaks
clear computes

Prompt
Prompt Tablespace Usage
Prompt

SET lines 120 pages 500 
col percent_used format 999.99

SELECT a.TABLESPACE_NAME,
  NVL(ROUND((a.BYTES  /1024)/1024/1024,2),2) GB_TOTAL,
  NVL(ROUND((b.BYTES  /1024)/1024/1024,2),2) GB_FREE,
  NVL(ROUND((b.largest/1024),2),0) KB_Chunk,
  NVL(ROUND(((a.BYTES -NVL(b.BYTES,1))/a.BYTES)*100,4),0) percent_used
FROM
  (SELECT TABLESPACE_NAME,
    NVL(SUM(BYTES),0) BYTES
  FROM dba_data_files
  GROUP BY TABLESPACE_NAME
  ) a,
  (SELECT TABLESPACE_NAME,
    NVL(SUM(BYTES),1) BYTES ,
    NVL(MAX(BYTES),1) 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;

输出

TABLESPACE_NAME                  GB_TOTAL    GB_FREE   KB_CHUNK PERCENT_USED
------------------------------ ---------- ---------- ---------- ------------
SYSTEM                                .84        .02       9216        97.36
SYSAUX                                .57        .05      32768        91.10
UNDOTBS1                              .06        .05      36864        23.13
USERS                                   0          0       4096        20.00

Answer 6

以下查询将有助于找出表空间的可用空间（以 MB 为单位）：

select tablespace_name , sum(bytes)/1024/1024 from dba_free_space group by tablespacE_name order by 1;

Answer 7

您可以通过以下方式检查表空间可用空间：运行此查询：

SELECT dfq.tablespace_name AS "Tablespace Name",
       dfq.totalspace AS "Total Size MB",
       (dfq.totalspace - dsq.totalusedspace) "Free Space MB",
       ROUND(100 * ((dfq.totalspace - dsq.totalusedspace) / dfq.totalspace))||'%' "Free Space %"
  FROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) totalspace
          FROM dba_data_files
         GROUP BY tablespace_name) dfq,
       (SELECT tablespace_name,
               ROUND(SUM(bytes) / (1024 * 1024)) AS totalusedspace
          FROM dba_segments
         GROUP BY tablespace_name) dsq
 WHERE dfq.tablespace_name = dsq.tablespace_name(+);

在上面的查询中，我们选择了每个表空间的总大小以及以 MB 为单位的可用空间以及以总大小百分比表示的可用空间。因此，您可以使用此百分比列来跟踪并在可用空间百分比达到 20% 或您想要的任何百分比时发出警报。

Answer 8

除非我弄错了，否则上面的代码不会考虑未分配的空间，因此如果您确实想知道何时会达到硬限制，则应该使用 maxbytes。

我认为下面的代码可以做到这一点。它将可用空间计算为“可用空间”+未分配空间。

select 
     free.tablespace_name,
     free.bytes,
     reserv.maxbytes,
     reserv.bytes,
     reserv.maxbytes - reserv.bytes + free.bytes "max free bytes",
     reserv.datafiles
from
    (select tablespace_name, count(1) datafiles, sum(maxbytes) maxbytes, sum(bytes) bytes from dba_data_files group by tablespace_name) reserv,
    (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) free
where free.tablespace_name = reserv.tablespace_name;

Answer 9

column pct_free format 999.99
select
     used.tablespace_name,
     (reserv.maxbytes - used.bytes)*100/reserv.maxbytes pct_free,
     used.bytes/1024/1024/1024 used_gb,
     reserv.maxbytes/1024/1024/1024 maxgb,
     reserv.bytes/1024/1024/1024 gb,
     (reserv.maxbytes - used.bytes)/1024/1024/1024 "max free bytes",
     reserv.datafiles
from
    (select tablespace_name, count(1) datafiles, sum(greatest(maxbytes,bytes)) maxbytes, sum(bytes) bytes from dba_data_files group by tablespace_name) reserv,
    (select tablespace_name, sum(bytes) bytes from dba_segments group by tablespace_name) used
where used.tablespace_name = reserv.tablespace_name
order by 2
/

Answer 10

以下是 Oracle SQL Developer 在其表空间视图中使用的查询

select a.tablespace_name as "Tablespace Name",
       round(a.bytes_alloc / 1024 / 1024) "Allocated (MB)",
       round(nvl(b.bytes_free, 0) / 1024 / 1024) "Free (MB)",
       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) "Used (MB)",
       round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) "% Free",
       100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) "% Used",
       round(maxbytes/1024 / 1024) "Max. Bytes (MB)"
from  ( select  f.tablespace_name,
               sum(f.bytes) bytes_alloc,
               sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
        from dba_data_files f
        group by tablespace_name) a,
      ( select  f.tablespace_name,
               sum(f.bytes)  bytes_free
        from dba_free_space f
        group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
union all
select 
       h.tablespace_name as tablespace_name,
       round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
       round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,
       round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,
       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
       100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
       round(sum(f.maxbytes) / 1048576) max
from   sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
where  p.file_id(+) = h.file_id
and    p.tablespace_name(+) = h.tablespace_name
and    f.file_id = h.file_id
and    f.tablespace_name = h.tablespace_name
group by h.tablespace_name
ORDER BY 2;

Answer 11

您还可以通过查看磁盘上文件的大小来粗略了解表空间的使用情况。

我的数据库是用最大范围创建的，每个 dbf 文件只能增长到 32gigs - 所以当最后一个达到 32gigs 时，您知道您即将用完空间并需要添加另一个。

Answer 12

您可以在这个有用的包中使用名为 tablespaces.sh 的脚本：
http://dba-tips.blogspot.com/2014 /02/oracle-database-administration-scripts.html