Oracle SQL

Oracle で各表領域の空き領域と断片化情報を表示する方法

Oracle で各表領域の空き領域と断片化情報を表示する方法

Oracle データベースの各表領域ごとの空き領域と断片化情報を表示する方法を記載します。この SQL は SYS ユーザーあるいは SYSTEM ユーザーで実行する必要があります。Oracle8i、Oracle9i、Oracle10g で動作確認済みです。

COMP SUM OF nfrags totsiz avasiz ON REPORT
BREAK ON REPORT

SET PAGES 100
COL tsname FORMAT     a16 JUSTIFY c HEADING 'Tablespace'
COL nfrags FORMAT 999,990 JUSTIFY c HEADING 'Free|Frags'
COL mxfrag FORMAT 999,999 JUSTIFY c HEADING 'Largest|Frag (MB)'
COL totsiz FORMAT 999,999 JUSTIFY c HEADING 'Total|(MB)'
COL avasiz FORMAT 999,999 JUSTIFY c HEADING 'Available|(MB)'
COL pctusd FORMAT     990 JUSTIFY c HEADING 'Pct|Used'

SELECT
  TOTAL.TABLESPACE_NAME    tsname,
  D                        nfrags,
  C / 1024 / 1024          mxfrag,
  A / 1024 / 1024          totsiz,
  B / 1024 / 1024          avasiz,
  (1 - NVL(B,0) / A) * 100 pctusd
FROM
  (SELECT
     SUM(bytes) A,
     tablespace_name
     FROM dba_data_files
     GROUP BY tablespace_name) TOTAL,
  (SELECT SUM(bytes) B,
     MAX(bytes) C,
     COUNT(bytes) D,
     tablespace_name
     FROM dba_free_space
     GROUP BY tablespace_name) FREE
WHERE
  TOTAL.TABLESPACE_NAME = FREE.TABLESPACE_NAME (+);

結果として表示される各列はそれぞれ次のようになります。

Tablespace         : 表領域名
Free Frags         : 空き領域における断片化数
Largest Frag (MB)  : 空き領域が断片化している場合、その最大サイズ(MB)
Total        (MB)  : 表領域が属するデータファイルのサイズ(MB)
Available    (MB)  : 表領域における使用可能エクステントのサイズ(MB)
PctUsed            : 表領域における使用率(%)

"空き領域における断片化数" (Free Frags) と、"空き領域が断片化している場合、その最大サイズ(MB)" (Largest Frag (MB)) を確認し、断片化を解消する際に役立ちます。