oracle 找出表空间上的可用空间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7672126/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find out free space on tablespace
提问by Avs
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.
我们的应用程序由于“ORA-01536：表空间的空间配额超出”而失败了几次，我们希望能够通过定期检查表空间上的可用空间并在它低于某个级别时发出警报来防止这种情况.
Is there any way to find out how much free space is left in a tablespace?
有没有办法找出表空间中剩余多少可用空间？
After some research (I am not a DBA), I tried the following:
经过一番研究（我不是 DBA），我尝试了以下方法：
select max_bytes-bytes from user_ts_quotas;
select sum(nvl(bytes,0)) from user_free_space;
but those queries return completely different results.
但这些查询返回完全不同的结果。
回答by yanjost
I use this query
我使用这个查询
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;
回答by better sql statement
A much more accurate SQL STATEMENT
更准确的 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;
回答by Gurupreet Singh Bhatia
There are many ways to check the size, but as a developer we dont have much access to query meta tables, I find this solution very easy (Note: if you are getting error message ORA-01653 ‘The ORA-01653 error is caused because you need to add space to a tablespace.')
有很多方法可以检查大小，但是作为开发人员，我们没有太多的权限来查询元表，我发现这个解决方案非常简单（注意：如果您收到错误消息 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);
Thanks
谢谢
回答by Ankit Agrawal
This is one of the simplest query for the same that I came across and we use it for monitoring as well:
这是我遇到的最简单的查询之一，我们也使用它进行监控：
SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024/1024 "FREE SPACE(GB)"
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
A complete article about Oracle Tablespace: Tablespace
回答by FORTRAN
Unless I'm mistaken, the above code does not take unallocated space into account, so if you really want to know when you'll hit a hard limit, you should use maxbytes.
除非我弄错了，上面的代码没有考虑未分配的空间，所以如果你真的想知道什么时候会达到硬限制，你应该使用 maxbytes。
I think the code below does that. It calculates free space as "freespace" + unallocated space.
我认为下面的代码可以做到这一点。它将可用空间计算为“可用空间”+ 未分配空间。
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;
回答by Joseph Amalraj
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
/
回答by cnd
You can also get a rough idea of table space usage by looking at the size of the files on your disk.
您还可以通过查看磁盘上文件的大小大致了解表空间的使用情况。
My DB is created with max extents, and each dbf file can only grow to 32gigs - so when the last one reaches 32gigs, you know you're about to run out of room and need to add another.
我的数据库是用最大范围创建的，每个 dbf 文件只能增长到 32gigs - 所以当最后一个达到 32gigs 时，你知道你即将用完空间，需要添加另一个。
回答by user3401360
You can use a script called tablespaces.sh inside this helpful bundle: http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html
您可以在这个有用的包中使用一个名为 tablespaces.sh 的脚本：http: //dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html
