Oracle表空间使用率监控

可以通过以下方式方便地找出监控Oracle表空间使用率的SQL：找了个测试库，确保只有一个用户连接，利用TOAD查看表空间的使用率，先

可以通过以下方式方便地找出监控Oracle表空间使用率的SQL：

找了个测试库，确保只有一个用户连接，利用TOAD查看表空间的使用率，先刷新share pool,再刷新查看表空间的使用率，此时，可以在share pool查看刚执行SQL，如下：

SELECT TS.TABLESPACE_NAME 表空间名,
TS.STATUS 状态,
TS.CONTENTS,
TS.EXTENT_MANAGEMENT,
SIZE_INFO.MEGS_ALLOC,
SIZE_INFO.MEGS_FREE,
SIZE_INFO.MEGS_USED,
SIZE_INFO.PCT_FREE,
SIZE_INFO.PCT_USED,
Round(SIZE_INFO.MEGS_USED*100 / SIZE_INFO.MAX) used_of_max, ---add by myself
SIZE_INFO.MAX
FROM (SELECT A.TABLESPACE_NAME,
ROUND(A.BYTES_ALLOC / 1024 / 1024) MEGS_ALLOC,
ROUND(NVL(B.BYTES_FREE, 0) / 1024 / 1024) MEGS_FREE,
ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024) MEGS_USED,
ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100) PCT_FREE,
100 - ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100) PCT_USED,
ROUND(A.MAXBYTES / 1048576) MAX
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,
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) SIZE_INFO,
SYS.DBA_TABLESPACES TS

WHERE TS.TABLESPACE_NAME = SIZE_INFO.TABLESPACE_NAME

以上包括临时表空间的监控，如果只需监控永久表空间，，则简单改写为：

set LINESIZE 100

col TABLESPACE_NAME format A20

select *
from
(
SELECT A.TABLESPACE_NAME,
ROUND(A.BYTES_ALLOC / 1024 / 1024) MEGS_ALLOC,
ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024) MEGS_USED,
ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0))*100/A.MAXBYTES) used_of_max,
ROUND((A.MAXBYTES - A.BYTES_ALLOC + NVL(B.BYTES_FREE, 0))/1048576) free_of_max,
ROUND(A.MAXBYTES / 1048576) MAX
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(+)
)size_info
where size_info.used_of_max > 80;

监控内容只需查看used_of_max、free_of_max，其分别是已使用空间占最大表空间百分比、剩余可扩展表空间大小。(以上脚本是监控表空间使用率超过80%的表空间。)

相关阅读：

Oracle Undo 镜像数据探究

Oracle 回滚(ROLLBACK)和撤销(Undo)

Undo 表空间损坏导致无法open

Undo表空间失败的处理方法

Oracle Undo表空间重建与恢复