oracle

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>

以下では表領域の種類と役割について補足しています

表領域はいくつかのデータファイルで構成されます

テーブルサイズやインデックスサイズが知りたくなったら以下でわかります

スポンサーリンク
コピペで使う