DB CONTROL中表空间监控的SQL-优快云博客

本文提供了一个详细的Oracle数据库表空间分析SQL脚本，该脚本能够有效地查询并展示各个表空间的总大小、已使用大小、使用率及剩余空间等关键信息。特别关注了Undo表空间的利用率，并提供了在RAC环境中不同实例查看Undo表空间利用率的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT /*+first_rows */
D.TABLESPACE_NAME,
NVL(A.BYTES / 1024 / 1024, 0) TOTAL_SIZE,
DECODE(D.CONTENTS,
'UNDO',
NVL(U.BYTES, 0) / 1024 / 1024,
NVL(A.BYTES - NVL(F.BYTES, 0), 0) / 1024 / 1024) USER_SIZE,
DECODE(D.CONTENTS,
'UNDO',
NVL(U.BYTES / A.BYTES * 100, 0),
NVL((A.BYTES - NVL(F.BYTES, 0)) / A.BYTES * 100, 0)) "USED%",
DECODE(D.CONTENTS,
'UNDO',
NVL(A.BYTES - NVL(U.BYTES, 0), 0) / 1024 / 1024,
NVL(F.BYTES, 0) / 1024 / 1024) FREE_SIZE,
D.STATUS,
A.COUNT,
D.CONTENTS,
D.EXTENT_MANAGEMENT,
D.SEGMENT_SPACE_MANAGEMENT
FROM SYS.DBA_TABLESPACES D,
       (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, COUNT(FILE_ID) COUNT
   FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
   FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
       (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
   FROM (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, STATUS
    FROM DBA_UNDO_EXTENTS
   WHERE STATUS = 'ACTIVE'
   GROUP BY TABLESPACE_NAME, STATUS
  UNION ALL
  SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, STATUS
    FROM DBA_UNDO_EXTENTS
   WHERE STATUS = 'UNEXPIRED'
   GROUP BY TABLESPACE_NAME, STATUS)
GROUP BY TABLESPACE_NAME) U
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+)
   AND D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
   AND D.TABLESPACE_NAME = U.TABLESPACE_NAME(+)
   AND NOT (D.EXTENT_MANAGEMENT = 'LOCAL' AND D.CONTENTS = 'TEMPORARY')
   AND D.TABLESPACE_NAME LIKE '%%'
UNION ALL
SELECT D.TABLESPACE_NAME,
       NVL(A.BYTES / 1024 / 1024, 0),
       NVL(T.BYTES, 0) / 1024 / 1024,
       NVL(T.BYTES / A.BYTES * 100, 0),
       (NVL(A.BYTES, 0) / 1024 / 1024 - NVL(T.BYTES, 0) / 1024 / 1024),
       D.STATUS,
       A.COUNT,
       D.CONTENTS,
       D.EXTENT_MANAGEMENT,
       D.SEGMENT_SPACE_MANAGEMENT
FROM SYS.DBA_TABLESPACES D,
       (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, COUNT(FILE_ID) COUNT
   FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) A,
       (SELECT SS.TABLESPACE_NAME,
        SUM((SS.USED_BLOCKS * TS.BLOCKSIZE)) BYTES
   FROM GV$SORT_SEGMENT SS, SYS.TS$ TS
WHERE SS.TABLESPACE_NAME = TS.NAME
GROUP BY SS.TABLESPACE_NAME) T
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+)
   AND D.TABLESPACE_NAME = T.TABLESPACE_NAME(+)
   AND D.EXTENT_MANAGEMENT = 'LOCAL'
   AND D.CONTENTS = 'TEMPORARY'
   AND D.TABLESPACE_NAME LIKE '%%'
ORDER BY 1

注意UNDO，由于 DBA_UNDO_EXTENTS底层x$的关系，在RAC的2个实例上查询UNDO的利用率不一样，实例1就看自己的undotbs1利用率，实例2看undotbs2的利用率。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8242091/viewspace-673831/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8242091/viewspace-673831/