【Oracle】複数スキーマのテーブル表領域やサイズ容量を一発で簡単に確認するSQL

快速ワーク
2019.06.08
スポンサーリンク

Oracleなどのデータベースを扱う際、容量を気にする必要がありますよね。

気付いたらパンクして表領域エラー。そんなことはありがちです。本番でシステム稼働中に発生したらあたふたしてしまいますね。

盛りだくさんのテーブルの各容量、いったいどの程度の割合で使用されているのか、気になる場面は結構あります。

自動拡張にしておけばあまり気にする必要はないかもしれませんが、キャパシティが限られていて、そうもいかない場合もあると思います。

データ使用量は逐一、手早く、簡単に確認できた方がよいですね。

限りある資源の中でデータベースを扱う場合、

このテーブル、どんだけ使ってんだよ。。。

どの表領域が一番使いまくってんの？

スキーマ単位での使用率は？

パーティション単位の使用量の内訳。。。

など、各スキーマ単位の使用率や個別テーブルの使用量を確実に意識する必要があると思います。

今回は、超簡単に表領域の詳細をわかりやすく確認する方法をご紹介したいと思います。

Oralceを扱う方々に少しでも参考になれば幸いです。

スポンサーリンク

Oracle表領域、サイズ、データ使用量をSQL一発で超簡単確認

その方法は、取得したい任意のスキーマ、または表領域を指定し、Oracleディクショナリから使用量などを抽出する汎用的なＳＱＬを流すだけです。

取得結果のサイズをわかりやすくするため、バイトをキロ(KB)、メガ(MB)、ギガ(GB)などに変換します。

取得したデータをExcelなどに貼り付けて確認してみてください。

表領域データ使用量取得ＳＱＬでわかること一覧
セグメント使用量イニシャル(INITIAL_EXTENT)件数(NUM_ROWS)備考
TABLE○○テーブル毎（件数はDBA_TABLESのNUM_ROWSから取得）
INDEX○-インデックス毎
TABLE PARTITION○○テーブルパーティションキー毎（件数はDBA_TABLESのNUM_ROWSから取得）
INDEX PARTITION○-インデックスパーティションキー毎
スキーマ別合計--複数スキーマ毎に総容量を知りたい場合
タイプ別合計--テーブルやインデックス毎に総容量を知りたい場合
表領域別合計--表領域毎に総容量を知りたい場合
スキーマ／タイプ別合計--スキーマとタイプ毎
スキーマ／表領域別合計--スキーマと表領域毎
スキーマ／タイプ／表領域別合計--スキーマとタイプと表領域毎

SQLで取得したデータをExcelに貼り付けて確認するイメージ画像

ObjectBrowserでSQLを実行して取得したクリップボードのデータをExcelに貼り付けた内容です。

あらかじめ条件付き書式を仕込んでます。貼り付け用のテンプレートを用意しておくと便利ですね。

Oracleのデータ使用量や表領域の情報を取得するSQL

ちょっと長いＳＱＬですが、全コピーして黄色のマーカー部分のスキーマ、または表領域指定の部分をご自分の環境に該当する値に変更して実行してみてください。

/* セグメント情報より表領域と使用量を確認するSQL */
with SEGMENT_DATA as (
  select
    ds.OWNER
   ,ds.SEGMENT_TYPE
   ,ds.SEGMENT_NAME
   ,ds.PARTITION_NAME
   ,ds.TABLESPACE_NAME
   ,ds.BYTES
   ,case
      when ds.BYTES / 1024 / 1024 / 1024 / 1024 >= 1 then round(ds.BYTES / 1024 / 1024 / 1024 / 1024, 3) || ' TB'
      when ds.BYTES / 1024 / 1024 / 1024 >= 1 then round(ds.BYTES / 1024 / 1024 / 1024, 3) || ' GB'
      when ds.BYTES / 1024 / 1024 >= 1 then round(ds.BYTES / 1024 / 1024, 3) || ' MB'
      when ds.BYTES / 1024 >= 1 then round(ds.BYTES / 1024, 3) || ' KB'
      else round(ds.BYTES, 3) || ' B'
    end as BYTES_TGMKB
   ,dt.NUM_ROWS
   ,ds.INITIAL_EXTENT
   ,case
      when ds.INITIAL_EXTENT / 1024 / 1024 / 1024 / 1024 >= 1 then round(ds.INITIAL_EXTENT / 1024 / 1024 / 1024 / 1024, 3) || ' TB'
      when ds.INITIAL_EXTENT / 1024 / 1024 / 1024 >= 1 then round(ds.INITIAL_EXTENT / 1024 / 1024 / 1024, 3) || ' GB'
      when ds.INITIAL_EXTENT / 1024 / 1024 >= 1 then round(ds.INITIAL_EXTENT / 1024 / 1024, 3) || ' MB'
      when ds.INITIAL_EXTENT / 1024 >= 1 then round(ds.INITIAL_EXTENT / 1024, 3) || ' KB'
      else round(ds.INITIAL_EXTENT, 3) || ' B'
    end as INITIAL_EXTENT_TGMKB
   ,null as TARGET_SUM
  from
    DBA_SEGMENTS ds,
    (select
       OWNER
      ,TABLE_NAME
      ,NUM_ROWS
     from
       DBA_TABLES
     union all
     select
       TABLE_OWNER as OWNER
      ,PARTITION_NAME as TABLE_NAME
      ,NUM_ROWS
     from
       DBA_TAB_PARTITIONS
    ) dt
  where
    ds.SEGMENT_NAME not like 'SYS_%' /* 除外 */
    and ds.OWNER = dt.OWNER(+)
    and nvl2(ds.PARTITION_NAME, ds.PARTITION_NAME, ds.SEGMENT_NAME) = dt.TABLE_NAME(+)
    /* スキーマ指定の場合 */
    and ds.OWNER in ('TEST','TEST01')
    /* 表領域指定の場合 */
    --and ds.TABLESPACE_NAME in ('TESTINDEX','TESTINDEX01','TESTUSERS','TESTUSERS01')
  order by
    ds.OWNER,case when ds.SEGMENT_TYPE like 'TABLE%' then 1 when ds.SEGMENT_TYPE like 'INDEX%' then 2 end
   ,ds.SEGMENT_NAME,ds.PARTITION_NAME,ds.TABLESPACE_NAME
    --ds.BYTES desc -- 使用量の大きい順の場合
)
-- スキーマ別合計
,SCHEMA_SUM as (
  select
    OWNER
   ,null as SEGMENT_TYPE
   ,null as SEGMENT_NAME
   ,null as PARTITION_NAME
   ,null as TABLESPACE_NAME
   ,sum(BYTES) as BYTES
   ,case
      when sum(BYTES) / 1024 / 1024 / 1024 / 1024 >= 1 then round(sum(BYTES) / 1024 / 1024 / 1024 / 1024, 3) || ' TB'
      when sum(BYTES) / 1024 / 1024 / 1024 >= 1 then round(sum(BYTES) / 1024 / 1024 / 1024, 3) || ' GB'
      when sum(BYTES) / 1024 / 1024 >= 1 then round(sum(BYTES) / 1024 / 1024, 3) || ' MB'
      when sum(BYTES) / 1024 >= 1 then round(sum(BYTES) / 1024, 3) || ' KB'
      else round(sum(BYTES), 3) || ' B'
    end as BYTES_TGMKB
   ,null as NUM_ROWS
   ,null as INITIAL_EXTENT
   ,null as INITIAL_EXTENT_TGMKB
   ,'スキーマ別合計' as TARGET_SUM
  from
    SEGMENT_DATA
  group by
    OWNER
  order by
    OWNER
)
-- 区切り
,SEPARATE as (
  select
    '--合計------------------------------------------------------------------------------' as OWNER
   ,null as SEGMENT_TYPE
   ,null as SEGMENT_NAME
   ,null as PARTITION_NAME
   ,null as TABLESPACE_NAME
   ,null as BYTES
   ,null as BYTES_TGMKB
   ,null as NUM_ROWS
   ,null as INITIAL_EXTENT
   ,null as INITIAL_EXTENT_TGMKB
   ,null as TARGET_SUM
  from
    dual
)
-- タイプ別合計
,TYPE_SUM as (
  select
    null as OWNER
   ,SEGMENT_TYPE
   ,null as SEGMENT_NAME
   ,null as PARTITION_NAME
   ,null as TABLESPACE_NAME
   ,sum(BYTES) as BYTES
   ,case
      when sum(BYTES) / 1024 / 1024 / 1024 / 1024 >= 1 then round(sum(BYTES) / 1024 / 1024 / 1024 / 1024, 3) || ' TB'
      when sum(BYTES) / 1024 / 1024 / 1024 >= 1 then round(sum(BYTES) / 1024 / 1024 / 1024, 3) || ' GB'
      when sum(BYTES) / 1024 / 1024 >= 1 then round(sum(BYTES) / 1024 / 1024, 3) || ' MB'
      when sum(BYTES) / 1024 >= 1 then round(sum(BYTES) / 1024, 3) || ' KB'
      else round(sum(BYTES), 3) || ' B'
    end as BYTES_TGMKB
   ,null as NUM_ROWS
   ,null as INITIAL_EXTENT
   ,null as INITIAL_EXTENT_TGMKB
   ,'タイプ別合計' as TARGET_SUM
  from
    SEGMENT_DATA
  group by
    SEGMENT_TYPE
  order by
    SEGMENT_TYPE
)
-- 表領域別合計
,TABLE_SPACE_SUM as (
  select
    null as OWNER
   ,null as SEGMENT_TYPE
   ,null as SEGMENT_NAME
   ,null as PARTITION_NAME
   ,TABLESPACE_NAME
   ,sum(BYTES) as BYTES
   ,case
      when sum(BYTES) / 1024 / 1024 / 1024 / 1024 >= 1 then round(sum(BYTES) / 1024 / 1024 / 1024 / 1024, 3) || ' TB'
      when sum(BYTES) / 1024 / 1024 / 1024 >= 1 then round(sum(BYTES) / 1024 / 1024 / 1024, 3) || ' GB'
      when sum(BYTES) / 1024 / 1024 >= 1 then round(sum(BYTES) / 1024 / 1024, 3) || ' MB'
      when sum(BYTES) / 1024 >= 1 then round(sum(BYTES) / 1024, 3) || ' KB'
      else round(sum(BYTES), 3) || ' B'
    end as BYTES_TGMKB
   ,null as NUM_ROWS
   ,null as INITIAL_EXTENT
   ,null as INITIAL_EXTENT_TGMKB
   ,'表領域別合計' as TARGET_SUM
  from
    SEGMENT_DATA
  group by
    TABLESPACE_NAME
  order by
    TABLESPACE_NAME
)
-- スキーマタイプ別合計
,SCHEMA_TYPE_SUM as (
  select
    OWNER
   ,SEGMENT_TYPE
   ,null as SEGMENT_NAME
   ,null as PARTITION_NAME
   ,null as TABLESPACE_NAME
   ,sum(BYTES) as BYTES
   ,case
      when sum(BYTES) / 1024 / 1024 / 1024 / 1024 >= 1 then round(sum(BYTES) / 1024 / 1024 / 1024 / 1024, 3) || ' TB'
      when sum(BYTES) / 1024 / 1024 / 1024 >= 1 then round(sum(BYTES) / 1024 / 1024 / 1024, 3) || ' GB'
      when sum(BYTES) / 1024 / 1024 >= 1 then round(sum(BYTES) / 1024 / 1024, 3) || ' MB'
      when sum(BYTES) / 1024 >= 1 then round(sum(BYTES) / 1024, 3) || ' KB'
      else round(sum(BYTES), 3) || ' B'
    end as BYTES_TGMKB
   ,null as NUM_ROWS
   ,null as INITIAL_EXTENT
   ,null as INITIAL_EXTENT_TGMKB
   ,'スキーマ／タイプ別合計' as TARGET_SUM
  from
    SEGMENT_DATA
  group by
    OWNER
   ,SEGMENT_TYPE
  order by
    OWNER
   ,SEGMENT_TYPE
)
-- スキーマ表領域別合計
,SCHEMA_TABLESPACE_SUM as (
  select
    OWNER
   ,null as SEGMENT_TYPE
   ,null as SEGMENT_NAME
   ,null as PARTITION_NAME
   ,TABLESPACE_NAME
   ,sum(BYTES) as BYTES
   ,case
      when sum(BYTES) / 1024 / 1024 / 1024 / 1024 >= 1 then round(sum(BYTES) / 1024 / 1024 / 1024 / 1024, 3) || ' TB'
      when sum(BYTES) / 1024 / 1024 / 1024 >= 1 then round(sum(BYTES) / 1024 / 1024 / 1024, 3) || ' GB'
      when sum(BYTES) / 1024 / 1024 >= 1 then round(sum(BYTES) / 1024 / 1024, 3) || ' MB'
      when sum(BYTES) / 1024 >= 1 then round(sum(BYTES) / 1024, 3) || ' KB'
      else round(sum(BYTES), 3) || ' B'
    end as BYTES_TGMKB
   ,null as NUM_ROWS
   ,null as INITIAL_EXTENT
   ,null as INITIAL_EXTENT_TGMKB
   ,'スキーマ／表領域別合計' as TARGET_SUM
  from
    SEGMENT_DATA
  group by
    OWNER
   ,TABLESPACE_NAME
  order by
    OWNER
   ,TABLESPACE_NAME
)
-- スキーマタイプ表領域別合計
,SCHEMA_TYPE_TABLESPACE_SUM as (
  select
    OWNER
   ,SEGMENT_TYPE
   ,null as SEGMENT_NAME
   ,null as PARTITION_NAME
   ,TABLESPACE_NAME
   ,sum(BYTES) as BYTES
   ,case
      when sum(BYTES) / 1024 / 1024 / 1024 / 1024 >= 1 then round(sum(BYTES) / 1024 / 1024 / 1024 / 1024, 3) || ' TB'
      when sum(BYTES) / 1024 / 1024 / 1024 >= 1 then round(sum(BYTES) / 1024 / 1024 / 1024, 3) || ' GB'
      when sum(BYTES) / 1024 / 1024 >= 1 then round(sum(BYTES) / 1024 / 1024, 3) || ' MB'
      when sum(BYTES) / 1024 >= 1 then round(sum(BYTES) / 1024, 3) || ' KB'
      else round(sum(BYTES), 3) || ' B'
    end as BYTES_TGMKB
   ,null as NUM_ROWS
   ,null as INITIAL_EXTENT
   ,null as INITIAL_EXTENT_TGMKB
   ,'スキーマ／タイプ／表領域別合計' as TARGET_SUM
  from
    SEGMENT_DATA
  group by
    OWNER
   ,SEGMENT_TYPE
   ,TABLESPACE_NAME
  order by
    OWNER
   ,SEGMENT_TYPE
   ,TABLESPACE_NAME
)
select * from SEGMENT_DATA
union all
select * from SEPARATE
union all
select * from SCHEMA_SUM
union all
select * from TYPE_SUM
union all
select * from TABLE_SPACE_SUM
union all
select * from SCHEMA_TYPE_SUM
union all
select * from SCHEMA_TABLESPACE_SUM
union all
select * from SCHEMA_TYPE_TABLESPACE_SUM
/

Oracle11gで上記SQLが問題ないことを確認しています。
10gでも試しましたが、なぜかエラーになりました。（ObjectBrowserの問題かもしれませんが）
その場合はメインのwith句内のselect文ならば問題ないので抜き出して実行してください。

最後に

Oracleのデータ量を確認するＳＱＬについて、ご紹介させていただきました。

全テーブルを一覧でわかりやすく管理できるようになると、どこにリスクがあり、どこに改善点があるのか、見つけやすくなると思います。

データベースを扱っている人にとっては、知っておいて損はないと思いますので、ぜひ参考にしてみてください。

快速ワーク
OracleSQL
スポンサーリンク
スポンサーリンク
caymezon
快 ブログ
スポンサーリンク

コメント

//▼2023/04/08追加 //https://lovagelab.com/posts/3406/ //▲2023/04/08追加