SQL oracle中的真实表空间大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2207122/
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
True tablespace size in oracle
提问by miki
I need to know truetablespace size in Oracle. I have some tablespace and I need to know how many space it uses now and how many space is free (and maybe percent of free space). I found in web some sqls but all of them showed size based on water mark... which is not true space allocated now but as far as I know the highest value which has ever been reached... So my real need is to know if I have enough space for my data which constantly are written and I must know how much of them I can store before having to delete some of them.
我需要知道Oracle 中的真实表空间大小。我有一些表空间，我需要知道它现在使用了多少空间以及有多少可用空间（可能还有可用空间的百分比）。我在 web 中发现了一些 sqls，但它们都显示了基于水印的大小......这不是现在分配的真正空间，但据我所知是曾经达到的最高值......所以我真正需要的是知道如果我有足够的空间来存储不断写入的数据，并且在删除其中一些数据之前，我必须知道我可以存储多少数据。
Thanks
谢谢
回答by Bob Jarvis - Reinstate Monica
Try this:
尝试这个：
-- Available space, by tablespace
SELECT * FROM
(SELECT tablespace_name FROM dba_tablespaces)
LEFT OUTER JOIN
(SELECT tablespace_name, SUM(bytes) AS total_bytes
FROM dba_data_files
GROUP BY tablespace_name)
USING (tablespace_name)
LEFT OUTER JOIN
(SELECT tablespace_name, sum(bytes) AS used_bytes
from dba_segments
GROUP BY tablespace_name)
USING (tablespace_name)
LEFT OUTER JOIN
(SELECT tablespace_name, SUM(bytes) AS free_bytes
FROM dba_free_space
GROUP BY tablespace_name)
USING (tablespace_name);
回答by Sumnibot
If you want to get an idea of data file size including those files that can auto extend then try:
如果您想了解数据文件大小，包括那些可以自动扩展的文件，请尝试：
SELECT DISTINCT a.tablespace_name,
sum(a.bytes)/1024/1024 CurMb,
sum(decode(b.maxextend, null, a.bytes/1024/1024, b.maxextend*(SELECT value FROM v$parameter WHERE name='db_block_size')/1024/1024)) MaxMb,
round(100*(sum(a.bytes)/1024/1024 - round(c.free/1024/1024))/(sum(decode(b.maxextend, null, a.bytes/1024/1024, b.maxextend*(SELECT value FROM v$parameter WHERE name='db_block_size')/1024/1024)))) UPercent,
(sum(a.bytes)/1024/1024 - round(c.free/1024/1024)) TotalUsed,
(sum(decode(b.maxextend, null, a.bytes/1024/1024, b.maxextend*(SELECT value FROM v$parameter WHERE name='db_block_size')/1024/1024)) - (sum(a.bytes)/1024/1024 - round(c.Free/1024/1024))) TotalFree
FROM dba_data_files a,
sys.filext$ b,
(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) free
FROM dba_tablespaces d,dba_free_space c
WHERE d.tablespace_name = c.tablespace_name(+)
GROUP BY d.tablespace_name) c
WHERE a.file_id = b.file#(+)
AND a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name,
c.free/1024
回答by Femme Fatale
Hopefully, this would assist you,
希望这会对你有所帮助，
SELECT a.tablespace_name, a.file_name, a.bytes allocated_bytes,b.free_bytes FROM dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space b GROUP BY file_id) b WHERE a.file_id=b.file_id
ORDER BY a.tablespace_name;
