DB CONTROL中表空間監控的SQL

westzq1984發表於2010-09-14
SQL
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章