表領域の使用状況を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>
