监控 Oracle XE 中的表空间使用情况
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5448309/
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
Monitoring tablespace usage in Oracle XE
提问by vtorhonen
As it says on the Oracle XE overview page:
正如Oracle XE 概览页面上所说：
Oracle Database XE can be installed on any size host machine with any number of CPUs (one database per machine), but XE will store up to 4GB of user data, use up to 1GB of memory, and use one CPU on the host machine.
Oracle 数据库 XE 可以安装在具有任意数量 CPU（每台机器一个数据库）的任何大小的主机上，但 XE 将存储多达 4GB 的用户数据，使用多达 1GB 的内存，并在主机上使用一个 CPU。
Now if I want to monitor the database to see how much user data is in use or how much memory is the database using, how would I do that? It is possible to monitor these values from Oracle Application Express, but I want to monitor the database from a centralized monitoring system. Oracle XE documentationpresents a query which returns Flash Recovery Area usage, so I'm guessing there's a similar query for user data usage also.
现在，如果我想监视数据库以查看正在使用的用户数据量或数据库使用的内存量，我该怎么做？可以从 Oracle Application Express 监视这些值，但我想从集中监视系统监视数据库。Oracle XE 文档提供了一个返回闪回恢复区使用情况的查询，所以我猜也有一个类似的用户数据使用查询。
SELECT
NAME,
TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE,'999,999,999,999')
AS SPACE_AVAILABLE,
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;
Also, what happens when user data is above the limit?
另外，当用户数据超过限制时会发生什么？
回答by Mac
Shamelessly taken from the Oracle FAQwebsite, here is a query that checks used space by tablespace:
无耻地取自Oracle FAQ网站，这是一个按表空间检查已用空间的查询：
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
By default, user data is the space used in the USERStablespace.
默认情况下，用户数据是USERS表空间中使用的空间。
As for what happens when you are at the limit, I can only guess that:
至于当你达到极限时会发生什么，我只能猜测：
- You will get some out of spaceerror when trying to insert data (some Oracle error message always seem to come from out of spaceanyway ;-)
- You may be in for the biggest check in your life...
- 尝试插入数据时，您会遇到一些空间不足的错误（无论如何，某些 Oracle 错误消息似乎总是来自空间不足；-）
- 你可能会面临人生中最大的一笔支票……
回答by ivan_pozdeev
A closely-related matter: tablespace usage by user (to find out where the space is going):
一个密切相关的问题：用户对表空间的使用（以找出空间的去向）：
select owner,tablespace_name,
sum(bytes)/1024/1024 as mbytes
from dba_segments
group by owner,tablespace_name
order by mbytes desc;
