表領域の監視は重要

Oracle DB の運用では、表領域の監視がとても重要。

表領域に所属するデータファイルの初期サイズを小さく定義している場合は、
データファイル自動拡張により一時的に使用率が下がるように見えるため、
監視閾値によっては、問題が表面化しにくいこともあるので注意。

特に SYSTEM 表領域が枯渇して sql*plus でログインすら出来ないという
事態になったら目も当てられない。

Enterprise Manager で確認する方法もあるが、ここでは SQL で取得する方法を紹介する。
表領域ごとの合計サイズ、使用サイズ、空きサイズ、使用率などの情報を参照するには、
データディクショナリの dba_data_files、dba_free_space を確認する。
※ set コマンドや、col コマンドはお好みで。



表領域の使用量、空き容量取得SQLサンプル

SQL>
set lines 120
set pages 100
set term off
tti off
clear col
col TABLESPACE_NAME     format a15
col "SIZE(MB)"          format a20
col "USED(MB)"          format a20
col "FREE(MB)"          format a20
col "USED(%)"           format 990.99
select
  tablespace_name,
  to_char(nvl(total_bytes / 1024,0),'999,999,999') as "size(MB)",
  to_char(nvl((total_bytes - free_total_bytes) / 1024 / 1024,0),'999,999,999') as "used(MB)",
  to_char(nvl(free_total_bytes / 1024 / 1024,0),'999,999,999') as "free(MB)",
  round(nvl((total_bytes - free_total_bytes) / total_bytes * 100,100),2) as "rate(%)"
from
  ( select
      tablespace_name,
      sum(bytes) total_bytes
    from
      dba_data_files
    group by
      tablespace_name
  ),
  ( select
      tablespace_name free_tablespace_name,
      sum(bytes) free_total_bytes
    from
      dba_free_space
    group by tablespace_name
  )
where
  tablespace_name = free_tablespace_name(+)
/

TABLESPACE_NAME SIZE(MB) USED(MB) FREE(MB) USED(%)
--------------- -------- -------- -------- -------
SYSTEM              1500     1472       14   98.12
SYSAUX              1266      789     1183   93.45
UNDOTBS1             500       26      474    5.38
HOGE                3335       70     3265    2.08

このようなスクリプトをジョブ化して閾値を設定しておけば、表領域使用量の増加傾向を踏まえ
データファイルの追加も出来るし、クリティカルな事態になる前にリカバリが可能だ。

削除しても表領域使用量が下がらない

このケースは、Delete コマンドで削除した場合に起こり得る事象であり、
HWM（高水位標、ハイウォーターマーク）が下がっていないことが原因。
(過去にデータが入っていたブロックは未使用領域として認識されていないため)

断片化は主に更新削除が多いテーブルで発生する。
エクステントの断片化は、SQL で読み込むブロック数も増えて IO が増加し、
クエリのパフォーマンス低下に繋がるので、解消しておきたい。

対処方法

truncate で削除が1番お手軽だが、そうもいかない場合は
alter table shrink space によるセグメント縮小や、
alter table move によるテーブル再作成などを検討する。