oracle 表領域の使用率と空き領域サイズを確認する

表領域の使用状況をSQLで確認

表領域のサイズ、空き領域のサイズは意外と頻繁に確認するものです。GUIで調べるのもいいのですが、やはりSQLでサクッと調べたいです。個人的にもこのSQLのコピペ使用率は高いです。

使用状況確認SQL

SELECT 
t.TABLESPACE_NAME
,ROUND(a.SPC /1024/1024,1) "全サイズM"
,ROUND(f.SPC /1024/1024,1) "空サイズM"
,decode(a.SPC-f.SPC,null,100,ROUND((a.SPC-f.SPC)/a.SPC * 100,2)) "使用率"
FROM
DBA_TABLESPACES t
left outer join 
(
  SELECT TABLESPACE_NAME,SUM(BYTES) SPC FROM DBA_DATA_FILES
  GROUP BY TABLESPACE_NAME
) a
on t.TABLESPACE_NAME = a.TABLESPACE_NAME
left outer join
(
  SELECT TABLESPACE_NAME,SUM(BYTES) SPC FROM DBA_FREE_SPACE
  GROUP BY TABLESPACE_NAME ORDER BY TABLESPACE_NAME
) f
on t.TABLESPACE_NAME = f.TABLESPACE_NAME
order by a.SPC desc nulls last
;

出力結果例

TABLESPACE_NAME            全サイズM     空サイズM 使用率
------------------------ ----------- ------------- ------
SYSAUX                          1070         155.0   85.5
TBS01                           1000         965.4    3.5
SYSTEM                           810           1.1   99.9
UNDOTBS1                         185         129.6   30.0
USERS                              5           3.2   36.3
TEMP                                                100.0

6行が選択されました。

SQL>

SQL*Plusでの表示用

COLUMN TABLESPACE_NAME FORMAT A24
COLUMN 全サイズM FORMAT 9999999990
COLUMN 空サイズM FORMAT 9999999990.9
COLUMN 使用率 FORMAT 990.99

解説および補足

いくつかのデータ・ディクショナリを結合しているので以下補足します。

DBA_TABLESPACES	表領域の情報がわかります。表領域の数だけレコードが存在します。
DBA_DATA_FILES	表領域毎のサイズがわかります。表領域を構成するデータファイル。
DBA_FREE_SPACE	表領域毎の空き情報がわかります（エクステント単位）外部結合しているのは使用率100%の時でも出力対象にしたいためです。

もし「ORA-00942: 表またはビューが存在しません。」とエラーが出てしまった場合はデータディクショナリ（DBA_FREE_SPACEやDBA_DATA_FILES）へのアクセス権限を保持していないと思われます。この場合SELECT_CATALOG_ROLEを付与してあげると解決します。但しロールの付与が反映されるには再度ログインし直す必要があります。
例）grant SELECT_CATALOG_ROLE to scott;

定期的に表領域の使用状況を確認しておきましょう

Oracleは表領域（TABLE SPASE）という論理的な記憶領域にテーブルやインデックスを格納しています。このためテーブルデータが大きくなると表領域内の空きスペースがなくなりORA-01653エラーが出てしまいます。このエラーが出るって事は「本来格納すべきデータが欠落する」って事なのでDBの最も重要な仕事ができていない事になります。普段から表領域の使用状況には気配りしておいて、使用率が高くなってきたら早めに表領域の拡張をお勧めします。

TEMP表領域のサイズは以下で求められます

通常の表領域（正式には永続表領域と呼称）と、UNDO表領域は「データファイル」で構成されていますが、TEMP（一時）表領域は「一時ファイル」で構成されており他と少し異なります。TEMP表領域のみ「空きサイズ」の取得ができないのはこの為で、TEMP表領域のみDBA_FREE_SPACEで管理されていません。

select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 M_BYTE,STATUS,AUTOEXTENSIBLE
from DBA_TEMP_FILES
ORDER BY TABLESPACE_NAME,FILE_NAME
;

SQL*Plusでの表示用

set lin 300
COLUMN TABLESPACE_NAME FORMAT A18
COLUMN FILE_NAME FORMAT A50
COLUMN M_BYTE    FORMAT 999990.9
COLUMN STATUS    FORMAT A8

出力結果例

SQL> select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 M_BYTE,STATUS,AUTOEXTENSIBLE
  2  from DBA_TEMP_FILES
  3  ORDER BY TABLESPACE_NAME,FILE_NAME
  4  ;

TABLESPACE_NAME    FILE_NAME                                             M_BYTE STATUS   AUTOEX
------------------ -------------------------------------------------- --------- -------- ------
TEMP               /usr/oracle/app/oradata/ORAU8/datafile/o1_mf_temp_     427.0 ONLINE   YES
                   hw3ohlh2_.tmp


SQL>