常用表空间&数据文件监控语句

来源：互联网发布：windows查看cpu温度编辑：程序博客网时间：2024/05/29 06:35

一、表空间及数据文件

SELECT TABLESPACE_NAME,
       FILE_NAME,
       BYTES / 1024 / 1024 "Total Size(MB)",
       AUTOEXTENSIBLE "Auto"
  FROM DBA_DATA_FILES
 ORDER BY TABLESPACE_NAME, FILE_ID;

二、表空间状态及其大小使用情况

SELECT  d.tablespace_name "Name", d.status "Status", d.contents "Type",
        TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size (MB)",
        TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0),0)/1024/1024, '99G999G990D900') "Used (MB)",
        TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Used%"
FROM sys.dba_tablespaces d,
    (select tablespace_name, sum(bytes) bytes
     from dba_data_files group by tablespace_name) a,
    (select tablespace_name, sum(bytes) bytes
     from dba_free_space group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
      AND d.tablespace_name = f.tablespace_name(+);

三、数据文件状态及其大小使用情况

SELECT A.TABLESPACE_NAME "TableSpace Name",
       A.FILE_NAME "File Name",
       A.STATUS "Status",
       A.AUTOEXTENSIBLE "Auto",
       TO_CHAR(NVL(A.BYTES / 1024 / 1024, 0), '99G999G990D900') "Size (MB)",
       TO_CHAR(NVL(A.BYTES - NVL(F.BYTES, 0), 0) / 1024 / 1024,
               '99G999G990D900') "Used (MB)",
       TO_CHAR(NVL((A.BYTES - NVL(F.BYTES, 0)) / A.BYTES * 100, 0),
               '990D00') "Used%"
  FROM DBA_DATA_FILES A,
       (SELECT FILE_ID, SUM(BYTES) BYTES
          FROM DBA_FREE_SPACE
         GROUP BY FILE_ID) F
 WHERE A.FILE_ID = F.FILE_ID(+)
 ORDER BY A.TABLESPACE_NAME, A.FILE_ID;

四、不使用临时文件的临时表空间

SELECT TABLESPACE_NAME, CONTENTS
  FROM DBA_TABLESPACES
 WHERE CONTENTS = 'TEMPORARY'
   AND TABLESPACE_NAME NOT IN (SELECT TABLESPACE_NAME FROM DBA_TEMP_FILES);

五、无效的数据文件(offline)

SELECT F.TABLESPACE_NAME, F.FILE_NAME, D.STATUS
  FROM DBA_DATA_FILES F, V$DATAFILE D
 WHERE D.STATUS = 'OFFLINE'
   AND F.FILE_ID = FILE#(+);

六、处于恢复模式的文件

SELECT F.TABLESPACE_NAME, F.FILE_NAME
  FROM DBA_DATA_FILES F, V$RECOVER_FILE R
 WHERE F.FILE_ID = R.FILE#;

七、表空间上的I/O分布

SELECT T.NAME      TS_NAME,
       F.NAME      FILE_NAME,
       S.PHYRDS    PHY_READS,
       S.PHYBLKRD  PHY_BLOCKREADS,
       S.PHYWRTS   PHY_WRITES,
       S.PHYBLKWRT PHY_BLOCKWRITES
  FROM GV$TABLESPACE T, GV$DATAFILE F, GV$FILESTAT S
 WHERE T.TS# = F.TS#
   AND F.FILE# = S.FILE#
 ORDER BY S.PHYRDS DESC, S.PHYWRTS DESC;

八、数据文件上的I/O分布

SELECT TS.NAME "Table Space",
       D.NAME "File Name",
       FS.PHYRDS "Phys Rds",
       DECODE(FSTOT.SUM_PH_RDS,
              0, 0,
              ROUND(100 * FS.PHYRDS / FSTOT.SUM_PH_RDS, 2)) "% Phys Rds",
       FS.PHYWRTS "Phys Wrts",
       DECODE(FSTOT.SUM_PH_WRTS,
              0, 0,
              ROUND(100 * FS.PHYWRTS / FSTOT.SUM_PH_WRTS, 2)) "% Phys Wrts"
  FROM V$FILESTAT FS,
       V$DATAFILE D,
       V$TABLESPACE TS,
       (SELECT SUM(PHYRDS) SUM_PH_RDS,
               SUM(PHYWRTS) SUM_PH_WRTS,
               SUM(PHYBLKRD) SUM_BL_RDS,
               SUM(PHYBLKWRT) SUM_BL_WRTS
          FROM V$FILESTAT) FSTOT
 WHERE D.FILE# = FS.FILE#
   AND D.TS# = TS.TS#;

九、为表空间添加数据文件

ALTER TABLESPACE BASE_DATA
  ADD DATAFILE '+DATA' SIZE 32767M AUTOEXTEND OFF;

十、表空间使用情况（所有）

SELECT NAME,
       MAX,
       TOTAL,
       USEDSPACE,
       USEDPCT,
       MAXUSEDPCT,
       FREESPACE,
       MAXFREESPACE
  FROM (SELECT /*+ALL_ROWS */
         D.TABLESPACE_NAME "NAME",
         ROUND(NVL(A.MAXBYTES / 1024 / 1024, 0)) "MAX",
         ROUND(NVL(A.BYTES / 1024 / 1024, 0)) "TOTAL",
         ROUND(DECODE(D.CONTENTS,
                      'UNDO',
                      NVL(U.BYTES, 0) / 1024 / 1024,
                      NVL(A.BYTES - NVL(F.BYTES, 0), 0) / 1024 / 1024),
               1) "USEDSPACE",
         ROUND(DECODE(D.CONTENTS,                      'UNDO',
                      NVL(U.BYTES / A.BYTES * 100, 0),
                      NVL((A.BYTES - NVL(F.BYTES, 0)) / A.BYTES * 100, 0)),
               1) "USEDPCT",
         ROUND(DECODE(D.CONTENTS,
                      'UNDO',
                      NVL(U.BYTES / A.MAXBYTES * 100, 0),
                      NVL((A.BYTES - NVL(F.BYTES, 0)) / A.MAXBYTES * 100, 0)),
               1) "MAXUSEDPCT",
         ROUND(DECODE(D.CONTENTS,
                      'UNDO',
                      NVL(A.BYTES - NVL(U.BYTES, 0), 0) / 1024 / 1024,
                      NVL(F.BYTES, 0) / 1024 / 1024),
               1) "FREESPACE",
         ROUND(DECODE(D.CONTENTS,
                      'UNDO',
                      NVL(A.MAXBYTES - NVL(U.BYTES, 0), 0) / 1024 / 1024,
                      NVL(A.MAXBYTES - (A.BYTES - NVL(F.BYTES, 0)), 0) / 1024 / 1024),
               1) "MAXFREESPACE"
          FROM SYS.DBA_TABLESPACES D,
               (SELECT TABLESPACE_NAME,
                       SUM(BYTES) BYTES,
                       SUM(DECODE(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) MAXBYTES,
                       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.MAXBYTES / 1024 / 1024, 0),
               NVL(A.BYTES / 1024 / 1024, 0),
               NVL(T.BYTES, 0) / 1024 / 1024,
               ROUND(NVL(T.BYTES / A.BYTES * 100, 0), 1),
               ROUND(NVL(T.BYTES / A.MAXBYTES * 100, 0), 1),
               (NVL(A.BYTES, 0) / 1024 / 1024 -
               NVL(T.BYTES, 0) / 1024 / 1024),
               (NVL(A.MAXBYTES, 0) / 1024 / 1024 -
               NVL(T.BYTES, 0) / 1024 / 1024)
          FROM SYS.DBA_TABLESPACES D,
               (SELECT TABLESPACE_NAME,
                       SUM(BYTES) BYTES,
                       SUM(DECODE(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) MAXBYTES,
                       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 4 DESC) A;

十一、UNDO使用情况

SELECT (SUM(BLOCKS) * 8) / 1024, STATUS
  FROM DBA_UNDO_EXTENTS
 GROUP BY STATUS;

0 0