表領域の使用率確認

はじめに

データベース内の各表領域の使用率を確認するクエリを残しておく。

内容

前提

実行環境は以下となる。

• OSバージョン
  • Windows Server 2016 Standard
• Oracle Databaseバージョン
  • Oracle Database 12C

利用場面

リアルタイムで、表領域にどれだけデータが入っているかを確認する時に便利な方法となる。
このクエリの結果を、定期的に履歴テーブルにINSERTしておくことで、使用率の推移を確認することもできる。

確認方法

以下のクエリを実行する。

SELECT
        TO_CHAR(SYSDATE, 'YYYYMMDD')
       ,TABLESPACE_NAME
       ,ROUND(NVL(MAX_BYTES / 1024 / 1024,0), 0) AS "最大サイズ(MB)"
       ,ROUND(NVL(TOTAL_BYTES / 1024 / 1024,0), 0) AS "拡張済サイズ(MB)"
       ,ROUND(NVL((TOTAL_BYTES - FREE_TOTAL_BYTES) / 1024 / 1024,0), 0) AS "使用サイズ(MB)"
       ,ROUND(NVL(FREE_TOTAL_BYTES / 1024 / 1024,0), 0) AS "空きサイズ(MB)"
       ,CASE
            WHEN MAX_BYTES = 0 THEN ROUND(NVL((TOTAL_BYTES - FREE_TOTAL_BYTES) / TOTAL_BYTES * 100,100),2)
            WHEN MAX_BYTES > 1 THEN ROUND(NVL((TOTAL_BYTES - FREE_TOTAL_BYTES) / MAX_BYTES * 100,100),2)
       END "使用率"
FROM
    (
     SELECT
             TABLESPACE_NAME
            ,SUM(BYTES) TOTAL_BYTES
            ,SUM(MAXBYTES) MAX_BYTES
     FROM DBA_DATA_FILES
     GROUP BY TABLESPACE_NAME
    ) DBA_DATA_FILES
LEFT JOIN
    (
     SELECT
            TABLESPACE_NAME FREE_TABLESPACE_NAME
           ,SUM(BYTES) FREE_TOTAL_BYTES
     FROM DBA_FREE_SPACE
     GROUP BY TABLESPACE_NAME
    ) DBA_FREE_SPACE
ON TABLESPACE_NAME = FREE_TABLESPACE_NAME

確認内容

基本的に使用率を確認する。
それ以外は必要に応じて確認をおこなう。
使用率がCASE文で分岐している理由は、ビックデータファイルとスモールデータファイルで、DBA_DATA_FILESのMAXBYTESに値が入るか否かが決まるためである。
それ以外に考慮ポイントはない。

まとめ

表領域は枯渇すると、ほとんどの場合で障害に発展するため、領域の使用率は逐一確認できるような状態にしておくことが望ましい。どのような方法であれ、表領域の枯渇をすぐに検知できるように、監視するためのクエリやツールは準備しておいた方が良い。