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博客

DB CONTROL中表空间监控的SQL

最新文章